C

How to Trick Vlookup to look in any direction

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
543
Office Version
365
Platform
Windows
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...
Read more about this Excel article...
 

Some videos you may like

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
Joined
Apr 26, 2018
Messages
471
Office Version
365
Platform
Windows
(y)(y)thanks for the sharing(y)(y)this trick will be helpful to beginners
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,172
Office Version
365
Platform
Windows
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)
 

Watch MrExcel Video

Forum statistics

Threads
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...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top