• If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
  • Excel articles and downloadable files provided in the articles have not been reviewed by MrExcel Publishing. Please apply the provided methods / codes and open the files at your own risk. If you have any questions regarding an article, please use the Article Discussion section.
C

How to Trick Vlookup to look in any direction

Excel Version
  1. 2011
  2. 2010
  3. 2007
Hello,

So this is my first Excel Article and I find it super informative for someone beginners like me who have to go through all the post of legends like @Aladin Akyurek @Eric W and many more to learn something new. So I thought i should write something looking at the recent post of Eric W

So Lets get Started,

In vlookup we have always known that the criteria or the Lookup value should always be on the left hand side of the Table array to return the value say

SALES RETURN 2020 (1).xlsx
CDEF
4Lookup ValueAreaMarks
5PunitRowta10
6AnkitMangaldai11
7NehaSilchar12
8
9
10Ankit11
11
12=VLOOKUP(C10,C4:E7,3,0)
13
14
15
Sheet1
Cell Formulas
RangeFormula
E10E10=VLOOKUP(C10,C4:E7,3,0)
E12E12=FORMULATEXT(E10)


But here is the trick, We can trick vlookup using functions like "if" and "choose'' to return values without keeping the criteria on the leftmost side of table. Which means the criteria column can be in any order to return the value as it is done in "lookup" formula

Consider this

So i have lookup value or the criteria in the rightmost side of the table array. What we did was we would create a helper column in the leftmost side and copy the lookup criteria and did the lookup.

Now we can skip this old-fashioned method with using if and choose function. So here's the example

SALES RETURN 2020 (1).xlsx
CDE
15AreaMarksLookup Value
16Rowta10Punit
17Mangaldai11Ankit
18Silchar12Neha
19
20Using if
21
22Ankit11
23=VLOOKUP(C22,IF({1,0},E16:E18,D16:D18),2,0)
24
25using choose function
26
27Ankit11
28=VLOOKUP(C27,CHOOSE({1,2},E16:E18,D16:D18),2,0)
29
Sheet1
Cell Formulas
RangeFormula
D22D22=VLOOKUP(C22,IF({1,0},E16:E18,D16:D18),2,0)
D23,D28D23=FORMULATEXT(D22)
D27D27=VLOOKUP(C27,CHOOSE({1,2},E16:E18,D16:D18),2,0)



The trick using if function i have learnt from @shaowu459 . It was amazing

Hope it might help someone like me who are beginners like me.
Author
CA_Punit
Views
6,526
First release
Last update
Rating
5.00 star(s) 3 ratings

Latest reviews

The First excel article

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top