• If you would like to post, please check out the MrExcel Message Board FAQ and click here to register.
    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

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.
Excel Version
2011, 2010, 2007
  • Like
Reactions: shaowu459
Author
CA_Punit
Views
167
First release
Last update
Rating
5.00 star(s) 1 ratings

Latest reviews

The First excel article

Some videos you may like

This Week's Hot Topics

Top