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

K

kcmuppet

Active Member
Joined
Nov 2, 2005
Messages
439
Office Version
  1. 365
Platform
  1. Windows
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
BCDEFGHIJ
6Prices for volumes up to:1001,00010,00050,000100,000
7TestPrice 1Price 2Price 3Price 4Price 5
8Cant sortTest 10.050.0480.030.020.011
9this list->Test 20.040.0290.0280.0210.01
10
11
12Lookup the price of:Test 2
13
14Price of 231 of Test 2 is:0.04<--Index & Match type 1 ("less than"): Not Correct
150.029<--Xlookup mode 1 ("exact match or next smaller") - Correct
16Price 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
170.04<--Xlookup mode 1 ("exact match or next smaller") : Correct
18Price 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
RangeFormula
D14D14=INDEX(Table4[[Price 1]:[Price 5]],MATCH(D12,Table4[Test],0),MATCH(231,D6:H6,1))
D15D15=XLOOKUP(231,D6:H6,XLOOKUP(D12,Table4[Test],Table4[[Price 1]:[Price 5]]),,1)
D16D16=INDEX(Table4[[Price 1]:[Price 5]],MATCH(D12,Table4[Test],0),MATCH(78,D6:H6,1))
D17D17=XLOOKUP(78,D6:H6,XLOOKUP(D12,Table4[Test],Table4[[Price 1]:[Price 5]]),,1)
D18D18=INDEX(Table4[[Price 1]:[Price 5]],MATCH(D12,Table4[Test],0),MATCH(153000,D6:H6,1))
D19D19=XLOOKUP(153000,D6:H6,XLOOKUP(D12,Table4[Test],Table4[[Price 1]:[Price 5]]),,1)


What formula would work for all permutations?
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
You're Xlookup is looking for an "exact match, or the next larger" not next smaller. Which should it be?
 
Upvote 0
Fluff said:
You're Xlookup is looking for an "exact match, or the next larger" not next smaller. Which should it be?
Click to expand...
Ah, my mistake thanks. If I use mode -1 it works for volume 231 or 153k examples but not for 78

Actually I don't mind which way round it it is, but I trying to find one formula that will work any number (volume) that is higher, lower or between those values in D6:H6 - i.e >1 but I don't want to have a artificial price column for a volume of 1.


Does that make sense?
 
Upvote 0
In that case if you change the value in H6 to 2,000,000 or any number greater than an expected order, you can then use your xlookup formula
 
Upvote 0
Fluff said:
In that case if you change the value in H6 to 2,000,000 or any number greater than an expected order, you can then use your xlookup formula
Click to expand...
Well...it did occur to me that I could create a spare column, at either end (either for a volume of 1, or a number higher than the largest possible order volume), but that seems a bit redundant. I was hoping that formula could manage with the existing thresholds: up to "n1", "up to n2" ,... "n5 and above"
 
Upvote 0
It's possible to do that, but why jumps through hoops & make your formula more complex, when you can just set the upper limit far higher than any expected value?
 
Upvote 0
I tried adding a column at either end, but still can't find a single formula that returns the expected result:

Book1.xlsx
CDEFGHI
6Prices for volumes up to:1001,00010,00050,000100,0002,000,000
7TestPrice 1Price 2Price 3Price 4Price 5Dummy
8Test 10.050.0480.030.020.011
9Test 20.040.0290.0280.0210.01
10
11Price of 231 of Test 2 is:0.04<--Index & Match type 1 ("less than"): Not Correct
120.04<--Xlookup mode -1 ("exact match or next smaller") - Not Correct
130.029<--Xlookup mode 1 ("exact match or next larger") : Correct
14Price of 78 of Test 2 is:#N/A<--Index & Match type 1 ("less than") - Not found
15#N/A<--Xlookup mode -1 ("exact match or next smaller") : Correct
160.04<--Xlookup mode 1 ("exact match or next larger") : Correct
17Price of 153,000 of Test 2 is:0.01<--Index & Match type 1 ("less than") - Correct
180.01<--Xlookup mode -1 ("exact match or next smaller") : Correct
190<--Xlookup mode 1 ("exact match or next larger") : Not Correct
Sheet2
Cell Formulas
RangeFormula
D11D11=INDEX(D8:I9,MATCH("Test 2",Table4[Test],0),MATCH(231,D6:I6,1))
D12D12=XLOOKUP(231,D6:I6,XLOOKUP("Test 2",Table4[Test],D8:I9),,-1)
D13D13=XLOOKUP(231,D6:I6,XLOOKUP("Test 2",Table4[Test],D8:I9),,1)
D14D14=INDEX(D8:I9,MATCH("Test 2",Table4[Test],0),MATCH(78,D6:I6,1))
D15D15=XLOOKUP(78,D6:I6,XLOOKUP("Test 2",Table4[Test],D8:I9),,-1)
D16D16=XLOOKUP(78,D6:I6,XLOOKUP("Test 2",Table4[Test],D8:I9),,1)
D17D17=INDEX(D8:I9,MATCH("Test 2",Table4[Test],0),MATCH(153000,D6:I6,1))
D18D18=XLOOKUP(153000,D6:I6,XLOOKUP("Test 2",Table4[Test],D8:I9),,-1)
D19D19=XLOOKUP(153000,D6:I6,XLOOKUP("Test 2",Table4[Test],D8:I9),,1)

Book1.xlsx
CDEFGHI
24Prices for volumes up to:01001,00010,00050,000100,000
25TestDummyPrice 1Price 2Price 3Price 4Price 5
26Test 10.050.0480.030.020.011
27Test 20.040.0290.0280.0210.01
28
29Price of 231 of Test 2 is:0.04<--Index & Match type 1 ("less than"): Not Correct
300.029<--Xlookup mode -1 ("exact match or next smaller") - Correct
31Price of 78 of Test 2 is:0<--Index & Match type 1 ("less than") - Not correct
320<--Xlookup mode -1 ("exact match or next smaller") : Not Correct
330.04<--Xlookup mode 1 ("exact match or next larger") : Correct
34Price of 153,000 of Test 2 is:0.01<--Index & Match type 1 ("less than") - Correct
350.01<--Xlookup mode -1 ("exact match or next smaller") : Correct
36#N/A<--Xlookup mode 1 ("exact match or next larger") : Not Correct
Sheet2
Cell Formulas
RangeFormula
D29D29=INDEX(D26:I27,MATCH("Test 2",Table43[Test],0),MATCH(231,D24:I24,1))
D30D30=XLOOKUP(231,D24:I24,XLOOKUP("Test 2",Table43[Test],D26:I27),,1)
D31D31=INDEX(D26:I27,MATCH("Test 2",Table43[Test],0),MATCH(78,D24:I24,1))
D32D32=XLOOKUP(78,D24:I24,XLOOKUP("Test 2",Table43[Test],D26:I27),"",-1)
D33D33=XLOOKUP(78,D24:I24,XLOOKUP("Test 2",Table43[Test],D26:I27),"",1)
D34D34=INDEX(D26:I27,MATCH("Test 2",Table43[Test],0),MATCH(153000,D24:I24,1))
D35D35=XLOOKUP(153000,D24:I24,XLOOKUP("Test 2",Table43[Test],D26:I27),,-1)
D36D36=XLOOKUP(153000,D24:I24,XLOOKUP("Test 2",Table43[Test],D26:I27),,1)
 
Upvote 0
You need to change H6
+Fluff 1.xlsm
CDEFGH
6Prices for volumes up to:1001,00010,00050,0002,000,000
7TestPrice 1Price 2Price 3Price 4Price 5
8Test 10.050.0480.030.020.011
9Test 20.040.0290.0280.0210.01
10
11
12Lookup the price of:Test 2
13
14Price of 231 of Test 2 is:0.029
15
16Price of 78 of Test 2 is:0.04
17
18Price of 153,000 of Test 2 is:0.01
19
Lists
Cell Formulas
RangeFormula
D14D14=XLOOKUP(231,D6:H6,XLOOKUP(D12,Table4[Test],Table4[[Price 1]:[Price 5]]),,1)
D16D16=XLOOKUP(78,D6:H6,XLOOKUP(D12,Table4[Test],Table4[[Price 1]:[Price 5]]),,1)
D18D18=XLOOKUP(153000,D6:H6,XLOOKUP(D12,Table4[Test],Table4[[Price 1]:[Price 5]]),,1)
 
Upvote 0
Then what should the formula if more than 100k units are ordered?
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
extracting data with sorted and subtotal
Replies
1
Views
92
earth
E
S
  • Solved
Help with a unit convert table with formulas and multiple index/match criteria
2
Replies
13
Views
658
Dave Patton
D
G
  • Question Question
XLookup's
Replies
1
Views
257
etaf
etaf
Asbestos_Jen
  • Question Question
Compare multiple parameters of samples to reference values (Excel 2007)
Replies
3
Views
392
Asbestos_Jen
Asbestos_Jen
Z
  • Solved
Excel VBA: Xlookup + Multi-Criteria + Copy to Windows Clipboard
Replies
7
Views
580
myall_blues
myall_blues

Forum statistics

Threads
1,224,828
Messages
6,181,201
Members
453,022
Latest member
RobertV1609

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top