C

How to Trick Vlookup to look in any direction

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
944
Office Version
  1. 365
Platform
  1. 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...
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
(y)(y)thanks for the sharing(y)(y)this trick will be helpful to beginners
 
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)
 
Can you please explain below part of formula

=VLOOKUP(C22,IF({1,0},E16:E18,D16:D18),2,0)

=VLOOKUP(C27,CHOOSE({1,2},E16:E18,D16:D18),2,0)

@CA_Punit
@Peter_SSs
 
and another question is can we use this for multiple columns? if yes then how?
 

Forum statistics

Threads
1,213,482
Messages
6,113,908
Members
448,532
Latest member
9Kimo3

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