Formula Friday #07
Formula Fridays are short reads, spotlighting a single formula that you can start using straight away.
Formula Fridays are short reads, spotlighting a single formula that you can start using straight away.
Let’s talk about the VLOOKUPs lesser half, the HLOOKUP
Before any frequent HLOOKUP users complain. I mean lesser half in the context of it not being used as frequently as the VLOOKUP. Most of the time, the data we extract will be in columns. Which means the VLOOKUP is the more obvious choice when you need to manipulate data. And/or extract details from the data.
HLOOKUPs are a really good accompaniment Function. Something you use in addition to another Function.
What does a HLOOKUP do?
HLOOKUP stands for horizontal lookup. If the VLOOKUP pulls data from a specific column, a HLOOKUP pulls data from a specific row.
When writing this post, I struggled to think of real world example where I have used a HLOOKUP on it’s own, to summarise performance data.
So we’re going with a hypothetical scenario for this one
Something which is likely to happen, and shows off the HLOOKUP in a way that you will find it useful. Let’s say we’re working with an Excel WSSI. And we want to always be able to see the previous week’s sales information, no matter what day we open the WSSI.
The formula we will use is this:
=HLOOKUP(“TY“,$D$6:$AE$391,MATCH((($B$3–7)-WEEKDAY(($B$3),1)+1),$D$6:$D$391,0),0)
It’s a HLOOKUP with some added drama. That makes it useful.
We’re going to go through it step by step
First off, for those who haven’t used an Excel WSSI before. Excel WSSIs typically have the week dates down the lefthand side and the various KPIs across the top. The WSSI holds the key to your Weekly, Sales, Stock and Intake. It is important to keep it up to date so you can manage your OTB (open to buy – money you have to buy more products to sell) and effectively trade your category or department.
In short, a Merchandisers best friend. Second only to a calculator.
The aim of this formula is to show the previous week’s sales, every time we open the WSSI. So whether we are looking at it on Monday morning or Friday afternoon we want to be able to see what the sales performance was for the previous week.
First we have to establish our lookup value. In this example it is “TY” – shorthand for this year in Merchandiser English. Not yet an official dialect, but it should be.
=HLOOKUP(“TY“
Your lookup value needs to be in the top most row of your reference table. In the same sense your lookup value for a VLOOKUP needs to be in the leftmost column.
Next we have to establish our reference table, also known as an array table.
=HLOOKUP(“TY“,$D$6:$AE$391
This outlines the area we want Excel to look at to return our value.
I know what you’re thinking
How does Excel know the difference between all the weeks on the WSSI?
That’s where the MATCH Function comes in. It will do all the heavy lifting, as far as counting the rows is concerned. So Excel knows exactly what week we want it to look at. The alternative would be counting the rows ourselves and updating the formula manually each week. Just thinking about it makes my head hurt.
Let’s hone in on the MATCH portion of this formula. What it does is pretty fantastic.
MATCH((($B$3–7)-WEEKDAY(($B$3),1)+1),$D$6:$D$391,0)
The final outcome of the MATCH function will be the row number of the week commencing date for the previous week. It will just need some help from us to get over the finish line. Please bare in mind, this part of the formula will only work if you have week commencing dates in your WSSI. If you just have week numbers, this part of the formula, in the way it’s written, won’t work.
If you like the idea of what this formula is doing and you want help to use it yourself. Feel free to drop me a line and we can work through it.
Okay, let’s get back to the task at hand.
One quick thing to note cell $B$3 in this example has the formula =TODAY() in it, which will show the current date.
First off the formula minuses 7 days from today’s date
MATCH((($B$3–7)
If today is December 2nd and you minus 7 days, you are on November 25th.
This next bit is what makes this part of the formula so special.
Today’s date is then “turned into” a reference number for the weekday it falls on. In this example, the first day of the week, and therefore the week commencing date, is a Sunday. Which means we want to use 1 as our return type, as this will mean Sunday = 1 and Saturday = 7.
MATCH((($B$3–7)-WEEKDAY(($B$3),1)
If we minus 4 from November 25th, the date becomes November 21st.
Which will make the date the Saturday of the week before last. So then you add 1, to bring you to November 22nd. The Sunday of the previous week and the reference date we need for our formula to work. Remember in this example the week commencing dates are all Sundays.
=HLOOKUP(“TY“,$D$6:$AE$391,MATCH((($B$3–7)-WEEKDAY(($B$3),1)+1)
The final part of the MATCH section is the array table Excel needs to look at to find which row has the date we have just calculated. In this example the dates are all in column D. So Excel will count, how many rows down from D6 is the answer to the formula which came before it. In this example it’s 309 rows.
=HLOOKUP(“TY“,$D$6:$AE$391,MATCH((($B$3–7)-WEEKDAY(($B$3),1)+1),$D$6:$D$391,0)
And we’re onto the home straight
Literally all we need to do know is close of the formula to make it an exact match and we are done.
=HLOOKUP(“TY“,$D$6:$AE$391,MATCH((($B$3–7)-WEEKDAY(($B$3),1)+1),$D$6:$D$391,0),0)
A very dramatic, but incredibly useful way to incorporate a HLOOKUP into your Excel WSSI.
If you would like any extra help on how you can incorporate more HLOOKUPs into your life feel free to drop me a line.
Until next time,
Ax
