Multiple xlookups not working

[Jaffabfc]

Hi,

I am wanting to lookup what is in column A:A to match cell O2 and what is in cells I1:T1 to match what is in cell O3 with the answer coming from I2:T1048576

I have tried Index Match i have tried sumifs (as it will only appear once) tried xlookup multiples but nothing just errors all time.

Can someone help?

I did post about it a while ago and got no response but i managed to fix it, since then the file got corrupt and i have had to start all over again and cant remember what formula i used.

Thanks

 

[hagia_sofia]

Hello, could you possibly post a sample of the data and how the output should look like?

 

[Jaffabfc]

Hi,
Thanks for coming back i seem to have got it working, i received an office update so i saved my work did the update then loaded it back up and now the formula is working.

So unsure what was going wrong, i knew i had the formula right but i didnt know if i had the wrong formatting but clearly was just excel playing up

 

[Alex Blakenburg]

Here are 2 options:

20250314 XLookup 2D Jaffabfc.xlsx
	N	O	P
1
2	ID	10
3	Column	Mar
4	Result	K10	TrimRange
5		K10	Without Trim Range
Sheet2

Cell Formulas
Range	Formula
O4	O4	=LET(data,DROP(TRIMRANGE(Sheet1!A:T,2,0),1), valCol,XLOOKUP(O3,Sheet1!A1:T1,data), result,XLOOKUP(O2,INDEX(data,0,1),valCol), result)
O5	O5	=LET(data,Sheet1!A2:T10000, valCol,XLOOKUP(O3,Sheet1!A1:T1,data), result,XLOOKUP(O2,INDEX(data,0,1),valCol), result)

My test data on Sheet1

20250314 XLookup 2D Jaffabfc.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	ID	Col2	Col3	Col4	Col5	Col6	Col7	Col8	Jan	Feb	Mar	Apr	May
2	2								I2	J2	K2	L2	M2
3	3								I3	J3	K3	L3	M3
4	4								I4	J4	K4	L4	M4
5	5								I5	J5	K5	L5	M5
6	6								I6	J6	K6	L6	M6
7	7								I7	J7	K7	L7	M7
8	8								I8	J8	K8	L8	M8
9	9								I9	J9	K9	L9	M9
10	10								I10	J10	K10	L10	M10
11	11								I11	J11	K11	L11	M11
12	12								I12	J12	K12	L12	M12
13	13								I13	J13	K13	L13	M13
Sheet1