xlookup with index and match

[Peter Davison]

I can't work out how I can use xlookup for a variable look up range based on a cell criteria?

I have a workbook 1 and sheet 1
I have a column (G) with a variable header in row 11 based on a drop down list selection
I'm trying to return a value from Workbook 2 Sheet 1 Range A35 to O1029 (with headings in row 34) which could be in columns F, I, L or O depending on the list selection above
So I am looking up a value in workbook 1 and sheet 1 Cell G12 and return in workbook 1 and sheet 1 Cell A12 what is in column A in Workbook 2 Sheet 1
So I can't see how I can use xlookup when my lookup column could be F, I, L or O.

If you have any thoughts it would be greatly appreciated.

Regards

 

[Radoslaw Poprawski]

provide file or small data sample.

 

[Peter Davison]

I'm not sure if this worked below I used the quick wrap
I can't add the XL2BB because last time i did that it affected some workbooks I had.

Workbook 1

Workbook 2

 

[Radoslaw Poprawski]

then provide small data sample from onedrive link or sharepoint

 

[Alex Blakenburg]

Just a heads up, I don't believe xlookup can access a "closed" workbook, so if you are expecting to have workbook 2 closed you might want to stay with index / match.

 

[Peter Davison]

I do have both workbooks open

 

[Radoslaw Poprawski]

@Peter Davison
Then please provide small data sample
If you want any look up function with criteria you have to add IF statement within.

This is the Table I learned it from (I'm not author of this data PMSOCHO is) he is Polish Excel trainer.

ex-285 - WYSZUKAJ.PIONOWO - kilka kryteriów - tablicowa z JEŻELI.xlsx
	B	C	D	E
1	Region	Storage	Product	code
2	East	M3	Lion	272x
3
4	Region	Storage	Product	code
5	West	M2	Twix	769x
6	East	M2	Mars	409x
7	West	M3	Lion	367x
8	West	M1	Twix	901x
9	West	M2	Mars	901x
10	East	M1	Lion	237x
11	West	M3	Twix	827x
12	West	M3	Mars	649x
13	East	M3	Lion	272x
14	West	M1	Lion	447x
15	East	M2	Twix	347x
16	East	M2	Lion	340x
17	West	M2	Lion	584x
18	East	M1	Mars	225x
19	East	M3	Mars	487x
20	East	M1	Twix	128x
21	East	M3	Twix	179x
22	West	M1	Mars	474x
ex-285 zrobione

Cell Formulas
Range	Formula
E2	E2	=VLOOKUP(B2,IF((C5:C22=C2)*(D5:D22=D2),B5:E22,""),4,0)
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E5:E22	Expression	=AND(D5=$D$2;C5=$C$2;B5=$B$2)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B2	List	East;West
C2	List	M1;M2;M3
D2	List	Mars;Lion;Twix

if you don't know which column number to use, that's fine, just in the col_index in VLOOKUP use Match function.
with XLOOKUP is bit more complicated.

If you might return multiple results then lookups are NOT the solution since they do not find duplicates (just the first found result)
in this case u must use = FILTER() function

 

[Alex Blakenburg]

See if this works for you.

You will need to change the Workbook & Sheetname of the Workbook2 reference.
(My preference would be use a table in workbook2 rather than hardcode row 1029)

20210622 Xlookup 2nd Workbook.xlsx
	A	B	C	D	E	F	G
1
2
3
4
5
6
7
8
9
10
11	ReturnValue						VariableHeader
12	Return this value						ItemToFind
13
14
Notes

Cell Formulas
Range	Formula
A12	A12	=XLOOKUP($G12,INDEX('[WorkBook2 Lookup Data.xlsx]Sheet1'!$A$34:$O$1029,0,MATCH($G$11,'[WorkBook2 Lookup Data.xlsx]Sheet1'!$A$34:$O$34,0)),'[WorkBook2 Lookup Data.xlsx]Sheet1'!$A$34:$A$1029,"Not Found",0)

 

[Peter Davison]

This is brilliant, thank you for all your help.
It is working great.

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.