How to Trick Vlookup to look in any direction

[CA_Punit]

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

 

[shaowu459]

thanks for the sharingthis trick will be helpful to beginners

 

[Peter_SSs]

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
	C	D	E
15	Area	Marks	Lookup Value
16	Rowta	10	Punit
17	Mangaldai	11	Ankit
18	Silchar	12	Neha
19
20
21
22	Ankit	11	VLOOKUP
23		11	INDEX/MATCH
24		11	XLOOKUP
Sheet8

Cell Formulas
Range	Formula
D22	D22	=VLOOKUP(C22,IF({1,0},E16:E18,D16:D18),2,0)
D23	D23	=INDEX(D16:D18,MATCH(C22,E16:E18,0))
D24	D24	=XLOOKUP(C22,E16:E18,D16:D18)

 

[Sufiyan97]

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

 

[Sufiyan97]

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