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.
|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.
|1||Name||Employee ID||Sales Region||Region Code||Sales||New Customers||Commission|
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.