VLOOKUP Formula Cheatsheet – FREE Download
We have talked a lot about VLOOKUP and other lookup formulas recently during VLOOKUP Week.
In this post I am giving away a free VLOOKUP formula cheatsheet for our readers. This cheatsheet is prepared by Cheater John specifically for our readers. I hope you enjoy the one page help on VLOOKUP.
Click here to download VLOOKUP Cheatsheet. [mirror]
More resources on VLOOKUP & Other Lookup Formulas:
 What is VLOOKUP Formula & How to use it?
 6 tips for writing better VLOOKUP formulas
 How make lookups to left? (INDEX + MATCH combination)
 Plain English explanation of VLOOKUP, OFFSET and MATCH formulas
 Looking up based on Multiple Criteria
 … more tips & tricks on vlookup formula
Thanks Cheater John
Thanks to Ates, the person behind Cheater John for preparing this beautiful VLOOKUP cheat sheet and sharing it with us. Visit his site if you are looking for more such cheat sheets.
If you enjoyed this cheatsheet, please take a minute to say thanks to Cheater John.
 
 

Leave a Reply
Scheduling Variable Feed Sources  Splitting a number into integer and decimal portions 
13 Responses to “VLOOKUP Formula Cheatsheet – FREE Download”
Hi Chandoo, I normally use Index which is much more powerful than vlookup. However, I learnt a new trick that allows you to use “Vlookup” to look up values to the left without helper columns, etc. I left it at your link:
http://chandoo.org/wp/2010/11/02/howtolookupvaluestoleft/
It was a bit late so you most likely have not seen it, anyway, you can use Choose() to look up columns to the left. In your example on that link the formula returns the value to the left:
=VLOOKUP(1088,CHOOSE({2,1},$B$5:$B$17,$D$5:$D$17),2,0) = “John”
Using Choose() allows you to look up values to the left.
regards, Mike
@Mike: in fact, I did notice your comment about using CHOOSE() to overcome VLOOKUP’s limitation. I wanted to congratulate you for finding such beautiful alternative. But I guess I must have lost in the flow of new comments. Thanks for the reminder and sharing it again.
@Mike and Chandoo. I would love to see a detailed explanation of this if you have the time. Thanks for the great print out it is up on my desk. I use look ups all the time but it is always nice to refer back to something.
thanks
The CheetSheet gave a very good explanation on 3 way lookups.
Thanks
@Godzilla
RE: =VLOOKUP(1088,CHOOSE({2,1},$B$5:$B$17,$D$5:$D$17),2,0)
.
The standard form of VLOOKUP is
=VLOOKUP( Value, Array, Col#, 0)
In the standard form VLookup looks for the Value in Column 1 of the array and then returns the matching value which is in Column Number, Col#
.
What Daddylonglegs has done with
=VLOOKUP( 1088, CHOOSE({2,1}, $B$5:$B$17,$D$5:$D$17), 2, 0)
.
Is to use Choose to define a 2 Column array in 2 parts with
CHOOSE({2,1}, $B$5:$B$17,$D$5:$D$17)
where Column 2 is defined to the left of Column 1.
.
Vlookup will go to Column 1 and look for the value and then go to Column 2 to find the matching value
.
It is worth noting that
=VLOOKUP(1088,CHOOSE({1,2},$D$5:$D$17,$B$5:$B$17),2,0)
returns the same value which is should.
.
The method can be extended to any number of columns
=VLOOKUP(2133,CHOOSE({1,2,3},$D$5:$D$17,$B$5:$B$17, $C$5:$C$17),3,0)
returns the No of Customers
Yes it is really quite amazing, I discovered it on excel forum where “daddylonglegs” used it and thought I would pass it on as I had always been under the impression that it was not possible to look up values to the left using vlookup without creating helper columns. I am sure that the way that “choose” works by creating an array in this fashion makes it usefull for other applications.
It is also helps to see how it is working by creating an array with it i.e. select a range and enter below into cell and creat array by entering cntr+shift+enter:
=CHOOSE({1,2,3},$D$5:$D$17,$B$5:$B$17, $C$5:$C$17)
You should see the values in the order that you selected them from {1,2,3}
Just shows you that you never stop learning.
Mike
PS your site is great chandoo and look forward to all your posts.
Here’s an advanced use of VLOOKUP that might help someone out…
I have a database of dates, employees, and hours worked. The table (data only) is a named range HTable, and each of the columns data are named ranges as well (Dates, Employees, and Hours), amongst others. The table gets sorted all sorts of ways, so there is no guarantee that any column will be in any order. To look up the hours worked by a certain employee (certain_Employee) on a certain date (certain_date), use this:
{=VLOOKUP(certain_date, HTable*IF(Employees=certain_Employee, 1, 0), 3, FALSE)}
Effectively, you are looking up first on column 2 (zeroing out all but the certain_Employee) and then on column 1.
Don’t forget to use ctrlshiftenter to get the braces.
Eric
[...] VLOOKUP Cheatsheet [...]
Hi john & chandoo
thanks
[...] Blog ini memiliki kumpulan artikel tentang penggunaan VLOOKUP yang lebih kompleks dan juga tersedia lembar petunjuk VLOOKUP untuk [...]
hi my friend :
pleased send for me a complete file to my email address that explain for me that use about – v look up function in excel .
truly yours
behnam danesh
july – 25 2012
[...] Blog ini memiliki kumpulan artikel tentang penggunaan VLOOKUP yang lebih kompleks dan juga tersedia lembar petunjuk VLOOKUP untuk [...]
[…] This link should help: http://www.contextures.com/xlFunctions02.html Chandoo is also quite good: FREE Excel VLOOKUP Formula Cheatsheet – Download Today  Chandoo.org – Learn Microsoft Excel Online […]