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]
Original VLOOKUP: =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:
=INDEX(mydata[thingamajig], MATCH($C$3,mydata[ID],0))
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] | |
| Formula | =VLOOKUP(something,'really fat data'!$A$2:$BK$123,53,FALSE) |
| 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] | |
| Formula | =VLOOKUP(something,mydata,53,FALSE) |
| 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() | |
| Formula | =VLOOKUP(something,mydata, MATCH("thingamajig",mydata[#Headers],0),FALSE) |
| Comments | Use MATCH() to find out which column header has the value you want to lookup. |
| Using INDEX + MATCH [help] | |
| Formula | =INDEX(mydata[thingamajig], MATCH($C$3,mydata[ID],0)) |
| 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…
Check out our complete guide to VLOOKUP and for more lookup pizzazz get my VLOOKUP book.
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.
13 Responses to “VLOOKUP that fat table with ease [3 quick tips]”
If you want to know a column number you can just use COLUMN() and pick literally any cell in the column you want to refer too (with an absolute reference if you want to drag the formula)...
Using COLUMN() limits you to tables starting in column A.
I have used COLUMNS() instead selecting the rows up to and including the required column.
This is particularly good, if you are referencing multiple columns and effectively extracting a record:
e.g. VLOOKUP($A$1,$D$10:$G$20,COLUMNS($D$10:E$10),0), which produces the matching value from the second column, when copied right will produce VLOOKUP($A$1,$D$10:$G$20,COLUMNS($D$10:F$10),0), which produces the matching value from the third column.
pS Of course the same technique can be used with INDEX and MATCH.
A long time ago in a Excel-sheet far, far away.... when you could still find vlookups in my workbook....
The only reason why leave a vlookup formula in a workbook, is because the owner of the document might not understand the match & index combo 🙂
Why VLOOKUP when you can PowerPivot?
@GDRIII: Interesting question. Unfortunately a majority of Excel users are still not familiar / have / use Power Pivot for most of their work. So VLOOKUP it is.
When you need a direct response... meaning, without clicking refresh, a vlookup (or alternative formula) is the way to go. But else, PQ anytime.
I love PQ, but I often find a lot my colleagues are in one of two sides on it.
1. Know Excel, but no clue about PQ. Don't know it even exists. Are afraid to even touch it.
2. Know TSQL (or other variants) and thumbs their nose at PQ. Thinks Excel is for accountants and not meant for tech savvy.
I know of only 1 other person in the company that uses PQ/PowerBI.
Me, I think each has it's own use and having more tool to pick from when working on a solution is a good thing. Trying to convince more people to pickup PQ.
As a quicker option to point 1, and especially when you are not starting your table in column A, when you are selecting your reference range, Excel (I'm using 2013) shows a pop-up showing the dimensions of your table e.g.: (50R x 20C); so as you scroll right selecting your table range you can note what the column number(s) of your desired columns are.
That's what I do all the time.
The tooltip near the cursor while selecting the lookup range gives a clear idea how many columns and rows are selected.
And it works in all versions of excel.
Voila.!!
Was just about to post the same thing! Great quick way of identifying the column number.
Brilliant!
I created a macro so I can toggle this from my quick access toolbar:
Sub ToggleReferenceStyle()
If Application.ReferenceStyle = xlA1 Then
Application.ReferenceStyle = xlR1C1
Else
Application.ReferenceStyle = xlA1
End If
End Sub
Sir;
I have all headers in left most column and in rows, sometimes the headers have duplicate entries, let's say there is header "Minority Interest" in Column one and Row fifteen, then again "Minority Interest" appears in Column one and Row Thirty Four.
Numerical data are in subsequent Columns for each header. I am using
VLOOKUP, and this function takes numerical data only for the Column one Row Fifteen "Minority Interest", and it ignores the Column one Row Thirty Four "Minority Interest" numerical data.
Can You guide me how to resolve this, data downloaded is of Financial Statements of companies, and Balance sheet or Profit loss account statements have additional headings at times.
Regards
Agnelo