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.
15 Responses to “Christmas Gift List – Set your budget and track gifts using Excel”
[...] Christmas Gift List – Set your budget and track gifts using Excel … [...]
I'm confused: if you spend $10, and your budget is $40, shouldn't the amount in the "Within Budget?" column stay black, since you didn't go over budget?
In other words, since we overspent on the electronic photo frame, shouldn't the $8 cell turn red?
@JP.. maybe Steven is encouraging consumerism... ?
I havent realized it earlier, but now I see it. If you unprotect the sheet, you can change the formula in Column I to =IF(G13=0;" ";F13-G13) from =IF(G13=0;" ";G13-F13), that should correct the behavior.
Thanks Chandoo. I thought of making a shopping list spreadsheet for Christmas, but this is neat so I think I'll use this instead.
Chandoo & Steven thanks for this spreadsheet. But for the sake of a person who has been staring at this megaformula in vain for the last 40 mins and not afraid to ask, would it be possible for you to walk us through the logic used here?
=SUM(SUMPRODUCT(SUBTOTAL(3,OFFSET($K$13:$K$62,ROW($K$13:$K$62)-MIN(ROW($K$13:$K$62)),0,1)),--($K$13:$K$62="-"))+SUMPRODUCT(SUBTOTAL(3,OFFSET($K$13:$K$62,ROW($K$13:$K$62)-MIN(ROW($K$13:$K$62)),0,1)),--($K$13:$K$62="0")))&" / "&SUBTOTAL(2,$G$13:$G$62)
Thanks Chandoo.. This is one of the best budget spreadsheets I've ever seen.. The Arrays are out of this world!! And it's FREE!!
Chandoo, can you tell us more about Steven? Does he have his own site?
JP, I think Chandoo changed it when he changed the currency formatting from £ to $, a negative figure is a good thing in this case. But don't change the formulas, the overbudget and under budget won't work properly if you do. Also Chandoo I think you've accidentally broke the conditional formatting for the alternating row colouring the formula is different to the version I sent you. As for the megaformula chrisham, it gave me a headache trying to get it all working, so I will let Chandoo talk you through it.
Hi,
In cells I6 and I7, I understand that subtotal together with offset function returns an array of ones after which, the sumproduct function gives the desired result.
But I’m not able to figure out the reason for using an array in I8 to return the most expensive gift.
Can’t the formula be just
“=VLOOKUP(SUBTOTAL(4,$G$13:$G$62),$G$13:$J$62,4,0)”
Savithri, Cell I8 needs the array, if the formula was “=VLOOKUP(SUBTOTAL(4,$G$13:$G$62),$G$13:$J$62,4,0)” it would find the highest price from the filtered range (i.e. highest actual in filtered range is $50) BUT then return the first person with that actual, not looking in just the filtered range (so first person on the list with a $50 actual.)
To see what I mean, change the formula, then change all the actuals to $50 then filter for baby, it lists the first name on the list.
But a good question 🙂
Thank you. I now realise that the array is used to get the ‘filtered range’ instead of the entire range, as table array for look up value.
[...] Download This Template [...]
this looks like an awesome excel sheet!! is there anyway i can get it emailed to me unprotected? for some reason, i am unable to download it 🙁 help!!
Hi I also can not download to a mac as the sheet is protected any help would be great
[...] to send her a pricey present. Rather, send a card with a picture of your child. Here’s a cool Excel sheet that will help you estimate your budget per person and let you track [...]
[...] husband and I pour/poor over the Christmas spreadsheet (yes, I do know how dorky that sounds, but we’re not the only ones!), figuring out who should give what to whom. We live at a distance from most of our family, so it [...]