Excel HLOOKUP Function
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.
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(LookupValue, SearchArea, RowNumber, [ApproximateMatch])
- 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.
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 |
=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.
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.
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.
Images Referenced
- https://excelexercises.com/logo2.png
- https://excelexercises.com/excel-functions/excelImages/logoGreenWhite.png