HLOOKUP – The less-know sibling of VLOOKUP

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…Excel Tips - HLOOKUP

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.

Excel Tips - HLOOKUP 1

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…

Excel Tips - HLOOKUP 2

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.Excel Tips - HLOOKUP 3

If you can manage VLOOKUP, there should not be any problem for you to manage HLOOKUP, as long as you know its existence.


About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Formula and tagged . Bookmark the permalink.

2 Responses to HLOOKUP – The less-know sibling of VLOOKUP

  1. Sam says:

    Thanks much for the great tutorial! Very helpful when putting together our own HLOOKUP tutorial for Eleven to Three.


Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.