Transposing Data with INDEX / MATCH combo what’s this all about?
OK imagine this – you have done the right thing as a budding data analyst and created your sales data down the page so that you can take advantage of Pivot Tables which likes dimensions of data going across in columns namely Date and Sales Value and the data itself including the transactional data namely the sales, by month going down the page. I labour on this point a little I know, but many beginners will show months and it’s data across the page. The problem with data arranged like this is you can’t take advantage of powerful Excel data tools like Pivot Tables, Autofilter, Advanced Filter or Power Query (or Get & Transform). So make it a habit to get data (and note I am not saying reports) in a database format, data down the page and dimensions of the data across.
So carrying on with my little scenario here, the Accountant storms across to your desk and says “What are you doing, this isn’t a report – give me a FY report with Jul 17 to Jun 18 month and sales across the page so someone in the real world can read it, now get to it!”
Digressing because it’s relevant to below – have I told you I love the INDEX function? It is a non volatile formula meaning its light on performance usage but it’s so powerful. I urge any budding Excel Power User to learn to use INDEX and its natural buddy MATCH so it’s second nature because it gives you great power over your spreadsheeting. See my earlier posts on this topic Why I like INDEX + MATCH and INDEX, Whats the Big Deal? You can pluck any number from anywhere on the grid to anywhere you want with INDEX / MATCH. Think of INDEX as your never tiring, always faithful golden retriever who goes bounding off to get whatever you point at and get it for you all day long, without tiring. I know people like VLOOKUP to do some of what I am talking about but INDEX + MATCH is more versatile and reliable and it’s easy to learn. I hardly ever use VLOOKUP now unless its for clients who prefer it because they know which is fair enough too.
*** Update 04/04/23 ***
Since I wrote this post a couple of years ago, astute Excelers will be wondering but hey INDEX / MATCH is old school now, what about the relatively new formula XLOOKUP? Yes, I would agree on this and now love using XLOOKUP and use it more than the above INDEX / MATCH . It does the same thing and more such as
- being able to search backwards
- it’s a more readable formula (1 formula vs 2)
- I find the match mode is easier to understand
- it has an inbuilt message handler.
The below approach using INDEX / MATCH still works great but of course XLOOKUP could be used if you want.
So back to my scenario, yes you could copy, then paste special, transpose values only. Yes I could use Power Query that’s true and probably will as my long term solution. But hey I am a data analyst and anything I do even quick fixes, I am going to automate in case I have to do it again. Automate or die should be one of your mantras.
So I get cracking – I add a data validation in G4 of the date column so I can have a dynamic starting point. If you were pushed for time G4 could just simply have 01/07/17 entered directly. I went the data validation route because next time my Accountant boss might want a report with 2 year’ s sales on a calendar year basis so I can easily choose another starting point in the validation drop down. With this starting date to my data set, we can use formulas to give us the dates we want using DATE function and then our INDEX formula can use these dates to to go and get the sales pertaining to the dates I want. Now I am assuming we want to report consecutive months in this type of report. So to get the months to increment by 1 across the page – I use the relative DATE formula in H4 =DATE(YEAR(G4),MONTH(G4)+1,DAY(G4)) – basically I am leaving the YEAR and DAY alone but incrementing the MONTH by one. Copy this across and of course the it will increment by 1 month. Now I use my INDEX / MATCH combo formula:
=INDEX($B$4:$C$24,MATCH(G4,$B$4:$B$24,0),2) and it uses the date in G4 in the MATCH function within the vector $B$4:$B$24
to return the position of the date and then return the corresponding value from the 2nd column within the array $B$4:$C$24 defined in the first part of the function. You could give the array and vector range names if you want. Also would make the array $B$4:$C$24 a n Excel Table so it’s dynamic for future data. Anyway this is a simple example – copied across the formula effortlessly returns the corresponding date’s sales.
