XLOOKUP with OFFSET

[Kra]

Hi all! Is it possible to create something like XLOOKUP without specified columns and return offset?



I need to look for value from cell C16 (in column C) and value from E16 (in range E3:L7) and show result from next column to the right. I tried to create OFFSET of XLOOKUP, but it shows error.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2			Value		Scenario1			Scenario2			Scenario3
3			1		A	10%		F	5%		K	100%
4			2		B	20%		G	15%		L	150%
5			3		C	30%		H	25%		M	200%
6			4		D	40%		I	35%		N	250%
7			5		E	50%		J	45%		O	300%
8
9
10
11
12
13
14
15			XLOOKUP		Result
16			1	B	20%
17			4	N	250%
18
Sheet2

 

[Peter_SSs]

I don't understand what C16 and C17 have to do with it. From your examples it looks like are just finding the value from D16 and d17 in the top section and returning the value from beside that.
So it just looks like this to me

22 10 31.xlsm
	C	D	E	F	G	H	I	J	K	L
1
2	Value		Scenario1			Scenario2			Scenario3
3	1		A	10%		F	5%		K	100%
4	2		B	20%		G	15%		L	150%
5	3		C	30%		H	25%		M	200%
6	4		D	40%		I	35%		N	250%
7	5		E	50%		J	45%		O	300%
14
15	XLOOKUP		Result
16	1	B	20%
17	4	N	250%
Lookup %

Cell Formulas
Range	Formula
E16:E17	E16	=SUMIF(E$3:K$7,D16,F$3:L$7)

 

[Kra]

I don't understand what C16 and C17 have to do with it. From your examples it looks like are just finding the value from D16 and d17 in the top section and returning the value from beside that.
So it just looks like this to me

22 10 31.xlsm
	C	D	E	F	G	H	I	J	K	L
1
2	Value		Scenario1			Scenario2			Scenario3
3	1		A	10%		F	5%		K	100%
4	2		B	20%		G	15%		L	150%
5	3		C	30%		H	25%		M	200%
6	4		D	40%		I	35%		N	250%
7	5		E	50%		J	45%		O	300%
14
15	XLOOKUP		Result
16	1	B	20%
17	4	N	250%
Lookup %

Cell Formulas
Range	Formula
E16:E17	E16	=SUMIF(E$3:K$7,D16,F$3:L$7)

Hi Peter_SSs, sorry - my mistake, I used wrong example. I need to find a value for both C16 and D16 - values from column E, H and K won't be unique - here is better example, sorry once again

Book1
	B	C	D	E	F	G	H	I	J	K	L	M
1
2		Value		Scenario1			Scenario2			Scenario3
3		1		A	10%		B	60%		C	110%
4		2		A	20%		B	70%		C	120%
5		3		A	30%		B	80%		C	130%
6		4		A	40%		B	90%		C	140%
7		5		A	50%		B	100%		C	150%
8
9
10
11
12
13
14
15		XLOOKUP		Result
16		1	B	60%
17		4	A	40%
18
19
Sheet1

 

[Peter_SSs]

Is this what you are looking for then?

Kra.xlsm
	C	D	E	F	G	H	I	J	K	L
2	Value		Scenario1			Scenario2			Scenario3
3	1		A	10%		B	60%		C	110%
4	2		A	20%		B	70%		C	120%
5	3		A	30%		B	80%		C	130%
6	4		A	40%		B	90%		C	140%
7	5		A	50%		B	100%		C	150%
8
14
15	XLOOKUP		Result
16	1	B	60%
17	4	A	40%
Sheet1

Cell Formulas
Range	Formula
C16:C17	C16	=FILTER(C$3:C$7,(E$3:E$7=D16)*(F$3:F$7=E16),FILTER(C$3:C$7,(H$3:H$7=D16)*(I$3:I$7=E16),FILTER(C$3:C$7,(K$3:K$7=D16)*(L$3:L$7=E16),"")))

 

[shaowu459]

how about this one:

工作簿1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2			Value		Scenario1			Scenario2			Scenario3
3			1		A	0.1		B	0.6		C	1.1
4			2		A	0.2		B	0.7		C	1.2
5			3		A	0.3		B	0.8		C	1.3
6			4		A	0.4		B	0.9		C	1.4
7			5		A	0.5		B	1		C	1.5
8
9
10	I need to find a value for both C16 and D16 - values from column E, H and K won't be unique - here is better example, sorry once again
11
12
13
14
15			XLOOKUP		Result
16			1	B	0.6
17			4	A	0.4
18
Sheet1

Cell Formulas
Range	Formula
E16:E17	E16	=SUM(IF((E$3:K$7=D16)*(C$3:C$7=C16),F$3:L$7))

 

[Kra]

Is this what you are looking for then?

Kra.xlsm
	C	D	E	F	G	H	I	J	K	L
2	Value		Scenario1			Scenario2			Scenario3
3	1		A	10%		B	60%		C	110%
4	2		A	20%		B	70%		C	120%
5	3		A	30%		B	80%		C	130%
6	4		A	40%		B	90%		C	140%
7	5		A	50%		B	100%		C	150%
8
14
15	XLOOKUP		Result
16	1	B	60%
17	4	A	40%
Sheet1

Cell Formulas
Range	Formula
C16:C17	C16	=FILTER(C$3:C$7,(E$3:E$7=D16)*(F$3:F$7=E16),FILTER(C$3:C$7,(H$3:H$7=D16)*(I$3:I$7=E16),FILTER(C$3:C$7,(K$3:K$7=D16)*(L$3:L$7=E16),"")))

Yes! Thank you!

 

[Peter_SSs]

I'm confused. You quoted my post (#4) and said "Yes! Thank you!" and yet you have marked another post, whose formulas do a completely different job to the post #4 formulas, as the the solution. Can you clarify the situation?

 

[Kra]

I'm confused. You quoted my post (#4) and said "Yes! Thank you!" and yet you have marked another post, whose formulas do a completely different job to the post #4 formulas, as the the solution. Can you clarify the situation?

Sorry, apparently my aiming for the correct solution button sucks. Corrected.