The Excel HLOOKUP function stands for horizontal lookup and is used to search for a specific value in a table. HLOOKUP retrieves a value in a specified row of the same column as the match value. For example, you could supply a column header as the lookup value and then specify the row number of the desired value. The HLOOKUP function is similar to the VLOOKUP function , but rather than looking from left to right, the HLOOKUP function searches from the top down.
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])
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.
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~?" |
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)
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)
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.
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)
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.