# How to Trick Vlookup to look in any direction

#### CA_Punit

##### Well-known Member
CA_Punit submitted a new Excel article:

How to Trick Vlookup to look in any direction - Vlookup criteria

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

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### shaowu459

##### Active Member
thanks for the sharingthis trick will be helpful to beginners

#### Peter_SSs

##### MrExcel MVP, Moderator
It is an interesting trick that I first saw in the forum by Barry Houdini. I have lost the actual earliest reference but DonkeyOte mentions Barry's use of the CHOOSE method way back here in 2010 and it was Barry who also used the IF form of the trick here in 2014. I am not suggesting that Barry necessarily invented these tricks, but that is how I first saw them.

However, I must say that I see it as a trick rather that a particularly useful tool. I think INDEX/MATCH to do the same thing is easier for non-experts to understand and amend if required. (It is also shorter).
Further, I think that both VLOOKUP and INDEX/MATCH may soon be obsolete for a lookup like this as the new XLOOKUP can do it even more simply and also has several built-in options for what to do if the lookup value is not found.

Examples:
20 07 30.xlsm
CDE
15AreaMarksLookup Value
16Rowta10Punit
17Mangaldai11Ankit
18Silchar12Neha
19
20
21
22Ankit11VLOOKUP
2311INDEX/MATCH
2411XLOOKUP
Sheet8
Cell Formulas
RangeFormula
D22D22=VLOOKUP(C22,IF({1,0},E16:E18,D16:D18),2,0)
D23D23=INDEX(D16:D18,MATCH(C22,E16:E18,0))
D24D24=XLOOKUP(C22,E16:E18,D16:D18)

1,102,241
Messages
5,485,597
Members
407,504
Latest member
inexperiencedOne

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...