Index Match / Xlookup modes for less than except...

[kcmuppet]

Hi

I'm trying a matrix 2 way lookup where the the columns are for prices based on volume thresholds

I've ben trying to find 1 formula that will work with one set of threshold numbers (D6:H6 in the example below), but I can't seem to geteither Index & Match or Xlookup to do it with one of the match types that works for numbers above or below the smallest or largest volume number

Book1
	B	C	D	E	F	G	H	I	J
6		Prices for volumes up to:	100	1,000	10,000	50,000	100,000
7		Test	Price 1	Price 2	Price 3	Price 4	Price 5
8	Cant sort	Test 1	0.05	0.048	0.03	0.02	0.011
9	this list->	Test 2	0.04	0.029	0.028	0.021	0.01
10
11
12		Lookup the price of:	Test 2
13
14		Price of 231 of Test 2 is:	0.04	<--Index & Match type 1 ("less than"): Not Correct
15			0.029	<--Xlookup mode 1 ("exact match or next smaller") - Correct
16		Price of 78 of Test 2 is:	#N/A	<--Index & Match type 1 ("less than") - Not found because there isn't a value in D2:DH smaller than 78
17			0.04	<--Xlookup mode 1 ("exact match or next smaller") : Correct
18		Price of 153,000 of Test 2 is:	0.01	<--Index & Match type 1 ("less than") - Correct
19			#N/A	<--Xlookup mode 1 ("exact match or next smaller") : Not found
Sheet2

Cell Formulas
Range	Formula
D14	D14	=INDEX(Table4[[Price 1]:[Price 5]],MATCH(D12,Table4[Test],0),MATCH(231,D6:H6,1))
D15	D15	=XLOOKUP(231,D6:H6,XLOOKUP(D12,Table4[Test],Table4[[Price 1]:[Price 5]]),,1)
D16	D16	=INDEX(Table4[[Price 1]:[Price 5]],MATCH(D12,Table4[Test],0),MATCH(78,D6:H6,1))
D17	D17	=XLOOKUP(78,D6:H6,XLOOKUP(D12,Table4[Test],Table4[[Price 1]:[Price 5]]),,1)
D18	D18	=INDEX(Table4[[Price 1]:[Price 5]],MATCH(D12,Table4[Test],0),MATCH(153000,D6:H6,1))
D19	D19	=XLOOKUP(153000,D6:H6,XLOOKUP(D12,Table4[Test],Table4[[Price 1]:[Price 5]]),,1)

What formula would work for all permutations?

 

[kcmuppet]

Ahh, I guess I'd need to put real values in it which are the same as for the previous column

 

[Fluff]

No you don't, if anything over 50k units you be sold at the 100k price, just make the change I suggested.

 

[kcmuppet]

Yes you're right.

However, in my scenario the volume thresholds are the published volume discounts of various suppliers which would cause a bit of confusion if I changed them. In theory it shouldn't be possible to order more than the largest volume listed, but in practice it happens so I need to be able to return a price rather than an error.

So for completeness, I found a way to do with a formula, without an extra column or changing the thresholds by setting the [match_mode] of the XLOOKUP to 1, and then exploiting the [not_found] option in XLOOKUP to repeat the formula but this time with the [match_mode] set to -1

ie: =XLOOKUP (lookup, lookup_array, return_array,XLOOKUP (lookup, lookup_array, return_array, , -1), 1)

Book1.xlsx
	C	D	E	F	G	H	I
24	Prices for volumes up to:	100	1,000	10,000	50,000	100,000
25	Test	Price 1	Price 2	Price 3	Price 4	Price 5
26	Test 1	0.05	0.048	0.03	0.02	0.011
27	Test 2	0.04	0.029	0.028	0.021	0.01
28
29	At volumes of:	65	993	8,956	48,000	58,000	120,000
30	the price of Test 2 is:	0.04	0.029	0.028	0.021	0.01	0.01
Sheet2

Cell Formulas
Range	Formula
D30	D30	=XLOOKUP(D29,D24:H24, XLOOKUP("Test 2",Table43[Test],Table43[[Price 1]:[Price 5]]), XLOOKUP(D29,D24:H24, XLOOKUP("Test 2",Table43[Test],Table43[[Price 1]:[Price 5]]),,-1),1)
E30	E30	=XLOOKUP(E29,D24:H24, XLOOKUP("Test 2",Table43[Test],Table43[[Price 1]:[Price 5]]), XLOOKUP(E29,D24:H24, XLOOKUP("Test 2",Table43[Test],Table43[[Price 1]:[Price 5]]),,-1),1)
F30	F30	=XLOOKUP(F29,D24:H24, XLOOKUP("Test 2",Table43[Test],Table43[[Price 1]:[Price 5]]), XLOOKUP(F29,D24:H24, XLOOKUP("Test 2",Table43[Test],Table43[[Price 1]:[Price 5]]),,-1),1)
G30	G30	=XLOOKUP(G29,D24:H24, XLOOKUP("Test 2",Table43[Test],Table43[[Price 1]:[Price 5]]), XLOOKUP(G29,D24:H24, XLOOKUP("Test 2",Table43[Test],Table43[[Price 1]:[Price 5]]),,-1),1)
H30	H30	=XLOOKUP(H29,D24:H24, XLOOKUP("Test 2",Table43[Test],Table43[[Price 1]:[Price 5]]), XLOOKUP(H29,D24:H24, XLOOKUP("Test 2",Table43[Test],Table43[[Price 1]:[Price 5]]),,-1),1)
I30	I30	=XLOOKUP(I29,D24:H24, XLOOKUP("Test 2",Table43[Test],Table43[[Price 1]:[Price 5]]), XLOOKUP(I29,D24:H24, XLOOKUP("Test 2",Table43[Test],Table43[[Price 1]:[Price 5]]),,-1),1)

 

[Fluff]

Glad you sorted it & thanks for the feedback.