XLOOKUP with multiple lookups vertical and horizontal

[aquapowers]

I'm trying to create the following lookup using XLOOKUP (or something else that works)

A1: K12 contains the data
Material in column A, quantity in B-F, prices in G-K.

The expected results are displayed in A16:A27.
The input field for each material is the quantity.
For example: Material 36580, input of 10 results in a price displayed in column C of 1267.75.
In this example, a quantity of 10 used the Q2 column quantity of 9, resulting in the P2 column of 1267.75.
The match mode for the quantity should be -1 (next smallest item in no exact match).

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Material	Q1	Q2	Q3	Q4	Q5	25p1	25p2	25p3	25p4	25p5
2	36580	3	9	18	0	0	1444.29	1267.75	1123.32	0.00	0.00
3	38358	1	0	0	0	0	634.10	0.00	0.00	0.00	0.00
4	73809	1	0	0	0	0	324.70	0.00	0.00	0.00	0.00
5	79123	3	9	18	0	0	1549.99	1360.54	1205.55	0.00	0.00
6	83419	1	0	0	0	0	347.53	0.00	0.00	0.00	0.00
7	88067	20	48	100	248	500	237.61	229.33	218.29	209.97	204.44
8	88068	32	48	100	240	0	297.57	273.03	245.02	228.17	0.00
9	88128	48	100	240	500	1000	164.29	152.34	141.83	134.34	127.66
10	88129	30	100	250	600	0	245.03	229.69	222.84	213.24	0.00
11	88189	250	500	0	0	0	30.04	27.34	0.00	0.00	0.00
12	88195	600	1000	0	0	0	13.40	13.13	0.00	0.00	0.00
13
14
15
16	Material	Qty	Price
17	36580	10	1267.75
18	38358	10	634.10
19	73809	10	324.70
20	79123	19	1205.55
21	83419	50	347.53
22	88067	170	209.97
23	88068	5000	228.17
24	88128	100	152.34
25	88129	251	213.24
26	88189	249	0.00
27	88195	500	0.00
Sheet1

Cell Formulas
Range	Formula
C17,C24	C17	=H2
C18:C19,C21	C18	=G3
C20	C20	=I5
C22:C23,C25	C22	=J7
C26:C27	C26	=XLOOKUP(B26,B11:F11,B11:F11,,-1)

 

[Eric W]

How about:

Book1
	A	B	C
16	Material	Qty	Price
17	36580	10	1267.75
18	38358	10	634.1
19	73809	10	324.7
20	79123	19	1205.55
21	83419	50	347.53
22	88067	170	218.29
23	88068	5000	228.17
24	88128	100	152.34
25	88129	251	222.84
26	88189	249	#N/A
27	88195	500	#N/A
Sheet5

Cell Formulas
Range	Formula
C17:C27	C17	=INDEX($G$2:$K$12,MATCH(A17,$A$2:$A$12,0),MATCH(B17,IFERROR(1/(1/(INDEX($B$2:$F$12,MATCH(A17,$A$2:$A$12,0),0))),9999999)))

Depending on your version of Excel (2019 or older) you may need to enter the formula with Control+Shift+Enter.

 

[aquapowers]

How about:

Book1
	A	B	C
16	Material	Qty	Price
17	36580	10	1267.75
18	38358	10	634.1
19	73809	10	324.7
20	79123	19	1205.55
21	83419	50	347.53
22	88067	170	218.29
23	88068	5000	228.17
24	88128	100	152.34
25	88129	251	222.84
26	88189	249	#N/A
27	88195	500	#N/A
Sheet5

Cell Formulas
Range	Formula
C17:C27	C17	=INDEX($G$2:$K$12,MATCH(A17,$A$2:$A$12,0),MATCH(B17,IFERROR(1/(1/(INDEX($B$2:$F$12,MATCH(A17,$A$2:$A$12,0),0))),9999999)))

Depending on your version of Excel (2019 or older) you may need to enter the formula with Control+Shift+Enter.

Thank you!! I get most of it, but can you explain how the IFERROR works. I'm stuck on that part.

 

[Eric W]

The IFERROR is sort of a trick in this case. Usually when you set up a range of values for a MATCH or LOOKUP function, especially if you're looking for the "exact number or next smallest item", the values must be in ascending sorted order. But you put 0 at the end of your ranges, e.g. 3,9,18,0,0. If you used 3,9,18,99999,99999 (where 99999 is some number bigger than any order you'll get), we wouldn't need the IFERROR. But in order to keep your original layout, I took the range of values that I found with the INDEX, say 3,9,18,0,0, and found the reciprocal (1/INDEX...), which turned that range into 1/3, 1/9, 1/18, #DIV/0, #DIV/0. Then I took the reciprocal again, giving 3,9,18,#DIV/0, #DIV/0. Finally the IFERROR kicks in, and converts the errors to 99999, giving 3,9,18,99999,99999, which is a range that MATCH can use.

Glad I could help!

 

[aquapowers]

Thank you for the explanation. It does make sense to me now. Brillant solution!!