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.
In my experience KPIs can broadly fall into two categories. I like to refer to them as vanity and performance KPIs. But the groupings are not as clear cut as you might think.
Some obvious Vanity KPIs could be the number of likes you get on a post, number of comments or number of followers on Social Media.
Some less obvious ones could be sales, email opens or newsletter sign-ups. Let me explain why.
If you have a dress that has taken £100k. Your highest cash driver. But 75% of those sales were driven by heavy discounts, is that dress better or worse than another dress which took £80k sales, with only 25% of sales driven by heavy discounts?
In this example, sales becomes the vanity KPI. As great as it is to take £100k sales. That £100k dress would not have driven as much profit as the £80k dress. Because it had a significantly higher proportion of sales at a reduced price.
Think of the HLOOKUP like the lesser known sibling of the famous VLOOKUP
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:
=VLOOKUP(37,B4:J21,HLOOKUP(“Net Sales”,B3:J3,1,0),0)
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
=VLOOKUP(37,B4:J21,6,0)
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
=VLOOKUP(37,B4:J21,HLOOKUP(G3,C23:J24,2,0),0)
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:
=INDEX(B3:J21,MATCH(37,B3:B21,0),MATCH(“Net Sales”,B3:J3,0))
Isn’t it wonderful!
We start the formula by indexing the whole table
=INDEX(B3:J21
We then specify what value we want to match in the left hand column
=INDEX(B3:J21,MATCH(37,B3:B21,0),
And finally we say what value we want to match in the topmost row
=INDEX(B3:J21,MATCH(37,B3:B21,0),MATCH(“Net Sales”,B3:J3,0))
Et Voilà.
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,
Ax
Like this post? You should see my newsletter.
Subscribe to the Excel & ME newsletter to get updates that will empower you to inform your business decisions with data.
