Table of Contents[Hide][Show]
Some time ago, we treated the VLOOKUP Function for vertical lookup as its name implies. This time around, we would be treating the HLOOKUP Function (and yes, it means horizontal lookup). The HLOOKUP Function in summary instructs excel to search for a certain value in a row, and then return a value on another row, but within the same column by moving down from that initial value according to the count specified by the user. Don't worry if this all sounds confusing to you because in this post, we would go over the HLOOKUP function with examples to help you understand it better.
Syntax & Argument
The syntax of the HLOOKUP Function is as follows:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
The structure of the HLOOKUP is similar to that of the VLOOKUP with 4 arguments (3 required and 1 optional).
- lookup_value: This is the value to lookup.
- Table_array: This represents the data table from which to retrieve the required data.
- row_index_num: Represents the row in the table to retrieve the data from. This is the only argument that slightly differs from the VLOOKUP Counterpart which is the Column index_num.
- [range_lookup]: Optional argument which is in effect a logical boolean indicating either FALSE for exact match lookups or TRUE for approximate Match lookups. The default if not selected is TRUE / approximate match, so please make sure to indicate if you need an exact match lookup.
Usage Notes
- The HLOOKUP Function can only search in the first row of the range or table array selected. Meaning that it would seek to match the lookup_value specified in the first row only.
- If the range_lookup argument is not provided, the default match would be the approximate match (TRUE). The function would match to the value in your array that is closest (but less than) your lookup_value.
- You would need to sort your data in ascending order when using the approximate match to avoid getting incorrect values.
- The HLOOKUP function is case insensitive, meaning that it pays no attention to lower or upper cases. It retrieves the value once the lookup value is found in the table array regardless of the difference in cases.
- HLOOKUP supports wildcards (?*) for finding partial matches.
HLOOKUP Examples
Approximate Match
In our example for this section, we have a table showing the bonus % applicable to each performance level, and for each performance level, we have the lower band of sales that each agent must make to be categorized into that performance level.
We can use an approximate match to lookup the performance level of the sales achieved by each agent and in effect the bonus % they are entitled to. Let's look at the steps below:
Step 1: Include a row number helper to make it easy to update the row_index_num argument and then Identify your lookup table and lookup value as seen below.
Step 2: In cell D4, type in the formula below for an approximate match of Regan Boyer's performance level. Then copy and paste the formula to cells below (Cells D5:D10)
Step 3: It's now time to match the applicable Bonus %. To do that, we only need to change the row_index_num use the formula below:
Step 4: Now copy and paste the formula to the rows below and Khalas!!
Exact Match
For this section, we would use the exact match HLOOKUP function to look up a rating from 1-5 and attach a performance level.
Because we are restricted to selecting one of the 5 ratings in this example, we can use the HLOOKUP function with exact match the return the agent's achieved performance level. The first (optional) step would be to include a row number helper and then Identify your lookup table and lookup value as seen below. You should however note that you don't need the row number helper as you can manually input the number for the row you desire in the lookup table.
Step 2: In cell D4, type in the formula below for an exact match of the performance level of Regan Boyer. Then copy and paste the formula to the cells below.
=HLOOKUP($C4,$H$4:$L$5,$N$5,0) // Use FALSE or 0 as the range lookup for exact Matches
Note that we didn't need to use the range_lookup argument for approximate matches because that is the default lookup. For exact matches however, we need to input FALSE or 0 (zero) as our range_lookup argument.