VLOOKUP, HLOOKUP & INDEX MATCH MATCH
Lookup Functions are one of the most used methods to display data in dashboards and concise summaries.
Very soon you will be able to do VLOOKUPs with your eyes closed
It’s hard to explain just how frequently a vlookup is used by Merchandisers/Digital Marketers. It is used so often that many job descriptions will specifically list it, along with Pivot tables, as either a desired or essential skill.
Once you understand how a VLOOKUP works it becomes an easy choice to use. A VLOOKUP is a vertical lookup. The aim is to find your lookup value in the leftmost column and return the related value in the specified column of your table array.
Now that’s all cleared up it will be much easier to start using the HLOOKUP Function.
Think of the HLOOKUP like the lesser known sibling of the famous VLOOKUP
HLOOKUP stands for horizontal look up. It works in the same way as the VLOOKUP but on it’s side. With a HLOOKUP your lookup value is in the topmost row and the returned value comes from a specified row below it.
Is it possible to combine the VLOOKUP and HLOOKUP Functions
Yes it is.
On the face of it, combining a VLOOKUP and HLOOKUP can give the impression of quickly and easily finding the values where the two lookup arrays meet. And it does. It absolutely does do that. But not quite how you might think.
Let’s walk through an example together.
Here we have our data in a pivot table. We have our week numbers down the left-handside and our metrics across the top.
For this example we will want to find what our net sales were in week 37.
For the VLOOKUP to work we need to have our lookup value in the leftmost column – check. And for the HLOOKUP to work we need to have the lookup value in the topmost row – check. Almost.
Unfortunately this formula:
Will only return the #VALUE! error
And that’s because when combining the VLOOKUP and HLOOKUP functions. The result of the HLOOKUP needs to represent the column value from where you want to return the result.
In short the HLOOKUP needs a numerical value to tell the VLOOKUP where to look
So instead of writing a VLOOKUP like this
Where 37 is the week number to lookup. B4:J21 is the table to reference (table array) and 6 is the column to return the value from.
Your formula would look like this
The HLOOKUP is used to determine what the column reference number is. And can only work if you are able to insert a row underneath your headings to add in the column count.
In this example our data was in a Pivot table. So the only way to get the formula to work was to create an additional table, below the data set. Which was used for the HLOOKUP to reference.
So it works, but it could be better.
What’s better than a VLOOKUP/HLOOKUP mash up – INDEX MATCH MATCH
I love love love this formula. Learning this formula elevated my excel game 10 fold. I mean, if you want to impress, this is the formula to go with.
More often than not, we will be presented with data that is in a grid (matrix) format.
Some businesses have databases that are linked directly to Pivot Tables, which auto update. Excel WSSIs are in grid format. And other BI tools such as Tableau, will often have the functionality to download the raw data that feeds the dashboard. And that too will be presented in a grid format.
The INDEX MATCH MATCH formula makes working with data in a grid format a lot easier.
As it does exactly what we have hoped the VLOOKUP/HLOOKUP mash up would do.
Going back to our previous Pivot Table. We want to find the net sales of week 37.
Our new formula looks like this:
Isn’t it wonderful!
We start the formula by indexing the whole table
We then specify what value we want to match in the left hand column
And finally we say what value we want to match in the topmost row
There are a number of ways to combine the INDEX and MATCH Functions. Which makes retrieving the answers you need a lot quicker and easier. The formula is also really flexible, therefore it doesn’t get “broken” or interrupted by additional columns which seem to appear out of nowhere.
I could gush about INDEX MATCH MATCH for hours. But I will save that for another day. I highly recommend testing it out as soon as you can. And if you get stuck, don’t forget you can always drop me a line.
Until next time,