HLOOKUP in Excel
The HLOOKUP function is the not as cool younger sibling of VLOOKUP. Learning how to do a HLOOKUP in Excel is the same as a VLOOKUP, the data is instead looked up horizontally rather than vertically.
There is a LOOKUP() but this is only hangs around as it’s compatible with old versions of Excel. The functions you really need to know are VLOOKUP and HLOOKUP and soon, you’ll want to learn all about XLOOKUP (yes, it’s as cool as it sounds).
So what do HLOOKUP and VLOOKUP actually do?
You’d use either to look for a particular value in a table or array of data and then return a corresponding value. In this tutorial, we’ll look at how to do a HLOOKUP as there are loads of VLOOKUP articles and videos available but not as many for HLOOKUP.
For example, in the table below there is a set of data that lists sales figures, company, city and salesperson. In this example, I want to know where the salesperson Hugo Matthews is located as highlighted:
Two things about this:
- With a small dataset, HLOOKUP or VLOOKUP is less useful. I can see the information I need without too much trouble here. But imagine there are thousands of salespeople and hundreds of rows of data. It could get pretty useful.
- In the real world, I have this data the other way round and I’d be using a VLOOKUP. Data arrays or tables work a lot better vertically than horizontally.
So, how would I return the value “Edinburgh” using a HLOOKUP? It would go something like this:
=HLOOKUP([select the thing I am looking up],[select the table or array of data],[select how many rows down I want Excel to look], [select exact or approximate match]
Here’s what that looks like using this example and I imagine at the moment you’re wondering what on earth is going on. I’ll explain…
1. Select the thing I am looking up
Excel needs to know what it is looking for. In the example, I’ve written out the name of the person I am looking for (Hugo Matthews) in cell D10. So I’m pointing Excel towards D10 as the place it needs to look for.
2. Select the table or array of data
Next, I need to tell Excel where to look. In this example, I’ve selected the array of data A2:K5. I’ve done that by dragging the cursor from A2, to K5. A different (and arguably better) way to do it is to make your data array a table and just reference that table.
3. Select how many rows down I want Excel to look
The next argument in the HLOOKUP function is telling Excel how many rows down to look. In a HLOOKUP Excel will always look for the item you are looking up in the first row of data. Likewise, for a VLOOKUP, it will always look in the first column. With this next part of the puzzle, you are telling Excel how many rows down from that first row you want it to look. In the example we’re using, we want to look 3 rows down to return the city (the first row counts as one!).
4. Select exact or approximate match
For me, this last argument in the HLOOKUP function is used the least. This is the exact or approximate match function. 99% of the time you want an exact match, i.e. you want Excel to exactly match what you are looking up in your table. To choose that you choose False or type 0.
Hopefully that all makes sense. Test it out on a smaller dataset before you start on larger data.
There are a few things to keep in mind about VLOOKUP and HLOOKUP, specifically a few pitfalls to avoid and consider.
- Multiple entries. If you have multiple entries for the item you are looking up you may not get the answer you are expecting. For example, if Hugo Matthews appeared multiple times in my data then the HLOOKUP would return the value from the first Hugo Matthews it came across (working from left to right). Likewise, with a VLOOKUP it works top to bottom.
- It can break. In my experience, it’s not the most robust of functions especially when used in conjunction with other functions and on large datasets. You may want to consider PivotTables or INDEX MATCH as an alternative.
On INDEX MATCH. It’s superior to both VLOOKUP and HLOOKUP in a few ways, so try to add that one to your toolkit as well. Below is a detailed look at our top 10 Excel formulas and functions and INDEX MATCH features alongside VLOOKUP and HLOOKUP
If you want a shorter tutorial, here’s a rundown from one of our Excel courses on HLOOKUP and VLOOKUP:
Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 90+ IT training courses.
