Many people talk about VLOOKUP and agree that it is one of the most useful functions in their work life with Excel. However, if you ask them about HLOOKUP, maybe 30% of them have no idea of its existence. The Google search result may reflect a little bit…
The result is not surprising at all given the nature how we are taught to organize data in a table. The rule of thumb: each row holds one record. Therefore we are likely to have a vertical table like the screenshot below which is ideal for VLOOKUP.
To answer a question like: What’s the gross value of box office for “Despicable Me 2?” is simple with VLOOKUP.
Nevertheless in real world, we are facing with too many varieties. Most of the time we work with data not so organized, nor not in the way we expected. There is a high chance that you end up with a horizontal table instead…
Technically, the table is not too bad. It’s just not good for VLOOKUP.
COPY and PASTE Transpose maybe something in your mind if you don’t know about HLOOKUP. Why? Because you want to transpose the table and make it good for VLOOKUP. (tips: just in case you do not know, you may transpose a table, i.e. convert a vertical table to horizontal; or vice versus, by Copy and Paste Special –> Transpose)
However after you read this post, HLOOKUP should be on the top of your mind if you need to figure out a question like: What’s the gross value of box office for “Despicable Me 2”?
The following table summarizes the differences between VLOOKUP and HLOOKUP.
If you can manage VLOOKUP, there should not be any problem for you to manage HLOOKUP, as long as you know its existence.