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
What formula would work for all permutations?
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?