Time for some good, old fashioned VLOOKUP love. Let’s say you are writing VLOOKUP()s to get data from an unusually fat table, ie one with heaps of columns. You want to get to lookup ID in first column and get thingamajig in what is that column number. Well, better get counting from 1 and after 19 seconds and lots of squinting you arrive at column number 53 – which has thingamajig.
If this sounds like your VLOOKUP routine, check out these three amazingly simple tips to save some time and effort with your lookups.
#1 Switch to R1C1 view
This is a quick and easy fix. Head to File > Options > Formulas and enable R1C1 reference style. This will tell you what each column is in number format. Problem solved.
Of course, this also means all your formula references will be turned in to R1C1 style. But once you disable the R1C1 reference style in Formula Options, your VLOOKUP will be back to A1 style.
#2 Use Tables
While the R1C1 view can quickly tell you what each column is in numbers, it won’t work if your data starts from 17th column or something like that. A better option is to turn your raw data in to tabular format using Insert > Table. Give this table a name from Design ribbon, like mydata. This way, you can use simpler lookup formulas.
[Related resources: VLOOKUP with Tables is awesome | Introduction to Excel Structural Referencing for table formulas]
=VLOOKUP(something, data!$A$2:$BK$123, 53,false)
VLOOKUP using tables:
=VLOOKUP(something, mydata, 53,false)
But we still have to figure out the column for thingamajig. Simple, we use MATCH() formula inside VLOOKUP, like this:
VLOOKUP using tables & MATCH():
=VLOOKUP(something, mydata, MATCH("thingamajig", mydata[#Headers], 0), false)
That is right, you can access table headers using the #Headers keyword and get position of any header.
#3 Use INDEX + MATCH
This will make the problem altogether irrelevant. Simply use INDEX and MATCH formulas to get the result you need, like this:
Now, you don’t care if thingamajig is 53rd column or 217th column and if ID is at the start or somewhere else. It works all the same.
For more about using INDEX in your formulas, check out this beautiful tutorial.
Here is a summary of all formula techniques
|Normal Lookup [help]|
|Comments||You need to know which column (53) has the data you need. Either count manually or enable R1C1 style to quickly to know.|
|Using Tables [help]|
|Comments||Create a table for your source data (using CTRL+T) and give it a name. Use the name in your formulas. You still need to know column number.|
|Using Tables + MATCH()|
|Comments||Use MATCH() to find out which column header has the value you want to lookup.|
|Using INDEX + MATCH [help]|
|Comments||Now, you don’t even need to know where everything is. Just lookup the value in ID column and get corresponding value from thingamajig column. Done 🙂|
Download Example Workbook
Click here to download workbook with all vlookup techniques discussed in this page. Play with formulas to learn more.
Get your VLOOKUP on…
How do you deal with fat tables?
I use tables and INDEX+MATCH whenever my data is more than a few columns or can change often.
What about you? How do you deal with several columns situation? Please post your tips in the comments section.