The knowledge of when to use VLOOKUP, HLOOKUP and the combination of INDEX & MATCH is a core skill of a very good Excel user. It can be the difference between suffering and smiling. Suffering was the condition I was in when I would make ten reports for our operations in ten African countries and make one master report that goes to the HQ team in Kenya. And smiling was the condition I was in when I figured out that I could make that one master report and have it automatically create the other ten reports mostly by using VLOOKUP, HLOOKUP and a combination of INDEX & MATCH.
I have already taken time to break down, using lots of illustration and screenshots, how you'll know you need VLOOKUP and how to use it. Now, let's move on to HLOOKUP. You can download the practice file here.
So I have created a sample sales table.
In the table, we have few sales people. But take the case of a multinational or a big FMCG company, there could be thousands of sales people. And once in a while, we would be interested in picking the sales performance of a particular employee for a particular month.
So how do we pick the Sales figure for an employee that we know his row and the month we are interested in? Well, it's not VLOOKUP we will use here. It's HLOOKUP.
HLOOKUP (horizontal lookup) helps us to look through a horizontal row of data for a particular field we are interested in (month in this example) and pick the value from another row far down (employee row we are interested in).
The syntax is
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
And in plain English it is:
=HLOOKUP(the_field_value_you_want, the_table_with_all_data, the_row_you_are_interested_in, what_Excel_should_do_if_it_doesn't_find_the_field_value)