How to use HLOOKUP in Excel
Excited to see how the HLOOKUP function works in Excel? Here’s an example.
The image below represents some details for four companies.
We have the sales, the expenses and the number of employees for each company.
Did you note that the Companies are organized horizontally ❓
Now we want to find the Sales for any selected company instantly. Can we do that using the HLOOKUP function?
For example, for Company A?
Let’s see here.
- Write the HLOOKUP function as follows:
= HLOOKUP (
- Write the lookup_value as the first argument of the HLOOKUP function as below.
We want to find the sales for Company A. And as we have Company A written in a cell so, we will simply create a reference to it 🔁
= HLOOKUP (B6,
You can also input the text string “Company A” as the lookup_value yourself.
- As the table_array argument, refer to the table where HLOOKUP will look for the lookup_value.
The table in our case ranges from Cell B1 to E4.
= HLOOKUP (B6, B1:E4
- As the row_index_num argument, specify the row number from where the matching value must be returned.
We want the sales for Company A to be returned. Sales sit in the second row in table B1:E4. So, we are setting the third argument to 2.
= HLOOKUP (B6, B1:E4, 2
- As the range_lookup argument, write:
TRUE: If you want Excel to run an approximate match. In this case, the HLOOKUP will look up the exact value (Company A) or the next largest value less than this value.
FALSE: If you want Excel to run an exact match. In this case, the HLOOKUP will look up the exact value (Company A). And if the exact value is not found in the given table, it will return the #N/A error.
The range_lookup argument is an optional one. If omitted, Excel by default sets it to TRUE – approximate match mode 🎨
In this case, we are setting range_lookup to FALSE as we want HLOOKUP to perform an exact match.
= HLOOKUP (B6, B1:E4, 2, FALSE)
- Press Enter, and there you go.
The HLOOKUP function fetches out the sales for Company A from the table above.
Simple enough 🙌