MVP #44: Excel VLOOKUP, HLOOKUP and the combination of INDEX & MATCH part 2

This is the continuation of the MVP #41: Excel VLOOKUP, HLOOKUP and the combination of INDEX & MATCH part 1 where I explained when you'll need to use VLOOKUP and how to use it.

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)

So let's specify the Salesperson row we are interested in (row 8 for Goke Imole) and Month (March).

The formula becomes

 =HLOOKUP("March",B3:G13,8,FALSE)

meaning look for the March field in the table B3:G13 and pick the data in row 8. The false is to tell Excel to complain if it doesn't see the March field.

And that's how HLOOKUP works.

Finally, let's link the HLOOKUP formula with the fields for the Salesperson Row and Month. So when we change them, the HLOOKUP will update the Sales Figure.

Update the formula from:  =HLOOKUP("March",B3:G13,8,FALSE) 

To: =HLOOKUP(C18,B3:G13,C17,FALSE)

And that's it!

Again, you can download the practice file here.

You now know how to use HLOOKUP. Next is harnessing the power of the combination of INDEX and MATCH.