Most of us have heard about the VLOOKUP function, but not so much about the HLOOKUP function in Excel.
What is HLOOKUP?
The HLOOKUP function in MS Excel searches for a value in the 1st row of a table and returns a value in the same column, from any row you specify.
>> Did you know that HLOOKUP stands for Horizontal Lookup while VLOOKUP stands for Vertical Lookup?
What is VLOOKUP?
The VLOOKUP function searches for a value in the 1st column of a table array and returns a value in the same row, from any column you specify.
Learn more about the VLOOKUP function in Excel and see examples in this article.
What is the HLOOKUP syntax?
The HLOOKUP function in Excel include 4 arguments: 3 are required and 1 is optional (range_lookup).
Syntax of the HLOOKUP function: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]).
• lookup_value – the value you want to find.
The value must be in the 1st row of the table or array you select.
• table_array – the range of cells in which the lookup_value and the return value you want to find are.
• row_index_num – the row number where the return value is, within your table_array.
The 1st row within your table_array is number 1.
• range_lookup – logical value specifying if you want the HLOOKUP function to find an exact match (0/FALSE) or an approximate one (1/TRUE).
If you don’t specify this argument, the default value is always 1/TRUE.
Notes
– We recommend using 0 as the range_lookup argument, to avoid unwanted results.
– Make sure the return value is below the row you search from. HLOOKUP can’t look upward!
– The value in the lookup_value argument can be a text, a formula or a reference.
– The return value will be 0 in Microsoft Excel if the cell containing the return value is empty. If the cell containing the value you need is a formula, the return value will be the result of the formula.
– Like for the VLOOKUP function, with HLOOKUP, you can use wildcard characters in the lookup_value argument if range_lookup is 0/FALSE and lookup_value is text. A question mark (?) will search for any single character and an asterisk (*) will match any sequence of characters.
– Uppercase and lowercase text values are equivalent.
Examples
Searching a value within the same table – reference
In this example, the lookup_value is a reference, the cell B6 mentioning the month of March.
Searching a value within the same table – specific value
In this example, the lookup_value is a specific value (March). For texts, lowercase or uppercase values are equivalent so, here, the lookup_value can be written “March” or “march”.
Searching a value within the same worksheet – wildcard
In this example, we are using the wildcard character * to save time: we know that only the month of March starts with M in English. We could use “M*” as lookup_value but in our example, we add “Month” within the table_array so we have to be a bit more specific to avoid any unwanted results.
If we wanted to find the name of the manager in charge during February, we could use “F*” as lookup_value to save time.
Need to use the HLOOKUP formula between two worksheets? Follow the same steps as for the VLOOKUP function while applying the HLOOKUP function syntax.
If a colleague ask you what’s the difference between HLOOKUP and VLOOKUP, now you know!
Learn more handy tips and Excel functions by joining our MS Excel Spreadsheet Course.
