Posted on November 19th, 2010 in Learn Excel - 19 comments

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.

### 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.

 Scheduling Variable Feed Sources Splitting a number into integer and decimal portions
 Written by Chandoo Tags: cheater john, cheatsheets, downloads, guest posts, Microsoft Excel Formulas, vlookup, vlookup week Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

1. Mike says:

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

2. Chandoo says:

@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.

3. godzilla says:

@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

4. Gopinath says:

The CheetSheet gave a very good explanation on 3 way lookups.

Thanks

5. Hui... says:

@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#
.
=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

6. Mike says:

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.

7. Eric says:

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

8. [...] VLOOKUP Cheat-sheet [...]

9. Gururaj says:

Hi john & chandoo

thanks

10. [...] Blog ini memiliki kumpulan artikel tentang penggunaan VLOOKUP yang lebih kompleks dan juga tersedia lembar petunjuk VLOOKUP untuk [...]

11. behnam says:

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

12. [...] Blog ini memiliki kumpulan artikel tentang penggunaan VLOOKUP yang lebih kompleks dan juga tersedia lembar petunjuk VLOOKUP untuk [...]

13. […] 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 […]

14. […] Blog ini memiliki kumpulan artikel tentang penggunaan VLOOKUP yang lebih kompleks dan juga tersedia lembar petunjuk VLOOKUP untuk […]

15. […] Blog ini memiliki kumpulan artikel tentang penggunaan VLOOKUP yang lebih kompleks dan juga tersedia lembar petunjuk VLOOKUPuntuk […]

16. […] Blog ini memiliki kumpulan artikel tentang penggunaan VLOOKUP yang lebih kompleks dan juga tersedia lembar petunjuk VLOOKUP untuk […]

17. […] Blog ini memiliki kumpulan artikel tentang penggunaan VLOOKUP yang lebih kompleks dan juga tersedia lembar petunjuk VLOOKUP untuk […]

18. Priyanka says:

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?

19. ramesh kumar says:

very easy & smooth process mathematics system.

 Scheduling Variable Feed Sources Splitting a number into integer and decimal portions