=VLOOKUP(......CHOOSE({...}.....incorporating Sheet Name Reference

[RapchikM]

Hello

Can someone help me to incorporate the correct syntax using VLOOKUP with CHOOSE{} with sheet Reference

=IFERROR(VLOOKUP(G3&H3,CHOOSE({1,2},B5:B101&C5:C101,E5:E101),2,0),"Not Found")

Where can i incorporate the 'Sheet3!' in above formula

Formula Entered and Matching Values of G3 and H3 are in Sheet 1 and obtaining data from range is in Sheet3

Thanks and Regards
RapchikM

 

[SunnyAlv]

Like this ?

i changed "CHOOSE" to "HSTACK" formula, and give sheet reference so u can rename sheets with ur sheets name

=IFERROR(VLOOKUP(Sheet1!G3&Sheet1!H3,HSTACK(Sheet3!B5:B101&Sheet3!C5:C101,Sheet3!E5:E101),2,0),"Not Found")

 

[felixstraube]

Can you explain what you want to achieve?

 

[RapchikM]

FelixStraube

=IFERROR(VLOOKUP(G3&H3,CHOOSE({1,2},B5:B101&C5:C101,E5:E101),2,0),"Not Found")

I've used the the above Formula in Sheet 3 to look up values of G3&H3. To Work Around with new function CHOOSE
Now Wanting to shift the Formula in Sheet1 But Main Data Remains From B5:E101 in Sheet 3

Many times we use VLOOKUP in the Current Sheet to Get or Match Value which is in the range of Different Sheet.
In similar way I was wanting to use VLOOKUP in the Current Sheet To Match the Value using CHOOSE with Range from Different Sheet.

I am not Trying anything New it is Just the Placement of Sheet Name in above Formula as this is my first time Working with CHOOSE...

By the Way CHOOSE is for Multi Criteria VLOOKUP

I need to try SunnyAlv solution

RapchikM

 

[RapchikM]

Guys

Very patiently i've resolved my self

 =IFERROR(VLOOKUP(G3&H3,CHOOSE({1,2},'Sheet3'!B5:B101&'Sheet3'!C5:C101,'Sheet3'!E5:E101),2,0),"Not Found")

I am not sure Whether HSTACK will be really helpful for the result i desired.


Thanks
RapchikM

 

[felixstraube]

I meant what your are trying to achieve with the CHOOSE function. I don't get it.

 

[Peter_SSs]

I meant what your are trying to achieve with the CHOOSE function. I don't get it.

It forces the two separate blue and red 'range' parts into a single 'range' which is required for VLOOKUP to work.
=IFERROR(VLOOKUP(G3&H3,CHOOSE({1,2},'Sheet3'!B5:B101&'Sheet3'!C5:C101,'Sheet3'!E5:E101),2,0),"Not Found")

@RapchikM
You could consider the more efficient (& shorter) XLOOKUP formula
=XLOOKUP(G3&H3,Sheet3!B5:B101&Sheet3!C5:C101,Sheet3!E5:E101,"Not Found")

 

[RapchikM]

Peter_SSs Sir,

It forces the two separate blue and red 'range' parts into a single 'range' which is required for VLOOKUP to work.
=IFERROR(VLOOKUP(G3&H3,CHOOSE({1,2},'Sheet3'!B5:B101&'Sheet3'!C5:C101,'Sheet3'!E5:E101),2,0),"Not Found")

This still further clarifies for VLOOKUP to work with CHOOSE

Thank you so much for showing one more way to achieve the desired result

=XLOOKUP(G3&H3,Sheet3!B5:B101&Sheet3!C5:C101,Sheet3!E5:E101,"Not Found")

BTW i was only aware for LOOKUP, VLOOKUP and HLOOKUP

Sir Under What circumstances would you prefer the followings
1. =VLOOKUP...(CHOOSE.... formula &
Or
2. =XLOOKUP......

Bit of Differences if you could explain

felixstraube
Below Link you may go through This will clarify
VLOOKUP.....CHOOSE..... MultiCriteria

Thanks
RapchikM

 

[felixstraube]

Oh now I see. Thanks. I actually never used CHOOSE(). Before, when i had excel 2019 or previous versions, I used INDEX and MATCH combination.
Now with XLOOKUP you can do the same with a single function.

I would go with XLOOKUP.

 

[Peter_SSs]

Sir Under What circumstances would you prefer the followings
1. =VLOOKUP...(CHOOSE.... formula &
Or
2. =XLOOKUP......

Along the lines mentioned by @felixstraube ..

• I would never prefer VLOOKUP/CHOOSE
• If XLOOKUP is available I would use it. (It is not available in all versions of Excel)
• If XLOOKUP is not available I would use INDEX/MATCH. For your example it would be

=IFNA(INDEX(Sheet3!E5:E101,MATCH(G3&H3,Sheet3!B5:B101&Sheet3!C5:C101,0)),"Not Found")

One further point in relation to that link you provided about VLOOKUP/CHOOSE. You have changed what was advised in that link in a way that could lead to incorrect results by leaving out the delimiter between the two values/columns. To demonstrate, in the example below I want to look up the value from col E where col B = "a" and Col C = "b". The changed formula structure that you used is shown in cell I2 and produces the incorrect result whereas the original structure proposed in the link page (cell J2) produces the correct result.

RapchikM_1.xlsm
	A	B	C	D	E	F	G	H	I	J
1							Col B	Col C	Col E
2		ab			1		a	b	1	2
3		a	b		2
4
5
Sheet2

Cell Formulas
Range	Formula
I2	I2	=VLOOKUP(G2&H2,CHOOSE({1,2},B2:B5&C2:C5,E2:E5),2,0)
J2	J2	=VLOOKUP(G2&"|"&H2,CHOOSE({1,2},B2:B5&"|"&C2:C5,E2:E5),2,0)