Excel HLOOKUP Function

Syntax of the HLOOKUP Function

The HLOOKUP function has 3 required arguments (input data separated by commas), and one optional argument. The syntax is as follows:

=HLOOKUP(Lookup_Value, Search_Area, Row_Number, [Approximate_Match])

• Lookup_Value (required) - The Lookup_Value is the value we know and will use to specify the correct column to search. For the HLOOKUP this will likely be a column header. The lookup_value can be a cell reference, number, a text string, or other type of value.
• Search_Area (required) - The Search_Area is a table of defined range in which the HLOOKUP will search for the result.
• Row_Number (required) - The Row_Number is used by Excel to determine the row from which the matched value will be returned.
• Approximate_Match [optional] - The Approximate_Match is a logical true/false value used by Excel to determine if you want to find an exact match or the nearest match for the Lookup_Value. The function's default value is TRUE, so if you want to use an exact match you must specify FALSE for the fourth argument. If FALSE, the function HLOOKUP will find an exact match and if none is found will return #N/A.

Important Note: The HLOOKUP function supports wildcard matching. When searching for Wildcards ("*" or "?") as a text argument it must be surrounded by double quotation marks (" ") in Excel.

Excel Wildcard Examples

	Wildcard	Meaning
1	?	Single character wildcard
2	*	Wildcard string of undefined length
3	~	Used when you want to use one of the wildcard characters as an actual criteria - this tells Excel that the character is actually part of the text and not a wildcard (e.g. a question mark is actually part of the text) Example: "What~?"

Examples using the HLOOKUP Function in Excel

Use this data for the following function examples.

	A	B	C	D	E	F	G
1	Name	Employee ID	Sales Region	Region Code	Sales	New Customers	Commission
2	Hugh Jackman	1002	East	124	$10,000	20	$1,000
3	Brad Pitt	1003	West	542	$5,000	4	$500
4	Scarlett Johansson	1005	West	542	$2,000	2	$200
5	Raymond Reddington	1006	East	124	$9,000	15	$900
6	Elizabeth Keen	1007	West	542	$15,000	25	$1,500
7	Idris Elba	1008	East	124	$12,000	22	$1,350

If I'm Raymond Reddington and I know my data is in row 5 of the table, I could use HLOOKUP to figure out my Sales Region. If we wanted to get to Sales Region data from row 5 in our Excel table above, we could write:

=HLOOKUP("Sales Region", A:G, 5, FALSE)

• Lookup_Value: "Sales Region" - The value we know we're looking for is Sales Region. It is the value the HLOOKUP function will use to determine which column to search.
• Search_Area: A:G - This is the range of the table specified, which is the range that will be checked for matches to our criteria condition.
• Row_Number: 5 - Excel will look for the specific row number, which indicates the row we want our search to be in. We know our data is in row 5.
• Approximate_Match: FALSE - This criteria is a boolean (true or false) and is used to specify if we want an approximate match on the lookup value. We know there is a table header called "Sales Region" so we'll specify FALSE and use an exact match.

The function HLOOKUP would return the text value "East". Excel first searches for our lookup value ("Sales Region") in the first row of the specified search area (A:G). It finds the lookup value in column C. Excel then looks down to row 5 in column C to return the value we're looking for.

If I'm Hugh Jackman and I know I'm in row 2 of the Excel table above, I can retrieve my Sales amount with the following formula utilizing the HLOOKUP function:

=HLOOKUP("Sales", A1:G7, 2, FALSE)

• Lookup_Value: "Sales" - This is the specific text we want HLOOKUP to search for to determine the correct column to search. This is where the HLOOKUP function will search for cell which contains the value "Sales" in the first row.
• Search_Area: A1:G7 - This is the range specified that covers our whole table.
• Row_Number: 2 - Excel will look for any value in row 2 in the same column as the lookup value in argument 1.
• Approximate_Match: FALSE - This criteria boolean is FALSE and therefore it will search for an exact match.

This HLOOKUP would return the value of $10,000. It checks our Lookup_Value argument and determines it needs to search in column E, then checks the row number and retrieves the value in the cell at row 2, column E, which contains $10,000.

Example Using Wildcard

Say I'm Idris Elba and I know that my data is in row 7 of the Excel table above. I want to know my employee ID number, but I've forgotten whether the name of the column is "Employee ID", "User ID", or simply "ID". I can use wildcard matching to return my ID number regardless of what the actual name of the column is:

=HLOOKUP("*id", A1:G7, 7, FALSE)

• Lookup_Value: "*id" - This is the specific wildcard text we want HLOOKUP to search for in our Search_Area. This is where the HLOOKUP function will search for the first cell that ends in "id" from left to right. The "*" means that there can be any (or no) characters before the characters "id".
• Search_Area: A1:G7 - This is the range specified that covers our whole table, which is the range that will be checked for matches to our criteria condition.
• Row_Number: 7 - Excel will look for any value in row 7 that matches with our Lookup_Value and is whithin the specific Search_Area.
• Approximate_Match: FALSE - This criteria boolean is FALSE and therefore it will search for an exact match.

The function would return the value of 1008. It will search from left to right the first row for our Lookup_Value "*id" and find column B. Then it will match our Lookup_Value with our Row_Number and match the result with our criteria as an exact match. Therefore, the exact match for our search is 1008.