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 cheat-sheet for our readers. This cheat-sheet is prepared by Cheater John specifically for our readers. I hope you enjoy the one page help on VLOOKUP.
Click here to download VLOOKUP Cheat-sheet. [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 cheat-sheet, please take a minute to say thanks to Cheater John.
21 Responses to “VLOOKUP Formula Cheat-sheet – 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/how-to-lookup-values-to-left/
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 ctrl-shift-enter to get the braces.
Eric
[...] VLOOKUP Cheat-sheet [...]
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 Cheat-sheet - Download Today | Chandoo.org - Learn Microsoft Excel Online […]
[…] Blog ini memiliki kumpulan artikel tentang penggunaan VLOOKUP yang lebih kompleks dan juga tersedia lembar petunjuk VLOOKUP untuk […]
[…] Blog ini memiliki kumpulan artikel tentang penggunaan VLOOKUP yang lebih kompleks dan juga tersedia lembar petunjuk VLOOKUPuntuk […]
[…] Blog ini memiliki kumpulan artikel tentang penggunaan VLOOKUP yang lebih kompleks dan juga tersedia lembar petunjuk VLOOKUP untuk […]
[…] Blog ini memiliki kumpulan artikel tentang penggunaan VLOOKUP yang lebih kompleks dan juga tersedia lembar petunjuk VLOOKUP untuk […]
I have 2 excel files. One has names that are checked and the other has the complete list. How do i use VLOOKUP to check those names that are done in the complete list?
very easy & smooth process mathematics system.
I have a real big problem at school with lookup and goal seek
From a yearly table (A) as below
EMP No month shares loan
10001 OPB Bal 10000 20000
10001 APR-23 10500 190000
10001 MAY-23 11000 180000
to
10001 MAR-24 16000 80000
This sheet contains entries for around 600 employees. For new year I need to copy the MAR-24 shares and loan figure in new sheet(B) for new year starting from OPB BAL, APR-24, MAY-24 to MAR 25.
I filter the sheet (A) on MAR-24 and copy each item individually,
If I copy the filtered cells, they get copied to the next sheet continuosly.
Can you suggest me better option to copy the MAR-24(A) row to new sheet(B) OPB BAL. This will help me a lot.
Laxmikant M Khairnar
Nasik - India