# 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
2,080
First release
Last update
Rating
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.

### Which adblocker are you using?

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

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