Multiple Criteria Index Match with One Exact and the Other closest but not greater than....

MR_J3NKO

New Member
Joined
Dec 18, 2018
Messages
6
Hi All.

I'm new to the forums but am confident in excel and have a conundrum i'm hoping I can get some assistance with.

I have a sheet containing transaction information (Sheet1) and a reference sheet (Sheet2) which lists items and price breaks. The number of price breaks is different per item.

Example of Sheet2: | = new column

Part Price Break Price
Item A | 1 | £1
Item A | 100 | £100
Item A |500 | £500
Item A | 1000 | £1,000
Item B | 50 | £20
Item B | 100 | £40
Item C | 100 | £1,000
Item C | 1000 | £10,000
Item C | 2000 | £20,000

In total, about 8000 lines with about 4000 items and differing (and overlapping) price breaks for each.

Now, on my sheet1, I have 18,000 lines of transactions and what I'm trying to do is bring across the relevant price break information pertinent to the transaction.

So, for example:

Transaction | Item Description | Qty Sold | My Required Answer (relevant price break to transaction)
Trans1 | Item A | 25 | 1
Trans2 | Item C | 90 | 100 - (relevant price break as not in excess of 1,000 units needed to qualify for next price)

Currently, i'm using INDEX/MATCHING to give me answers as VLOOKUP with a TRUE (using a concatenation of Item and Quantity doesn't work due to the descriptions being VERY similar between items. I can't share specifics but 1 item could be 11B0010 and another would be 11B0011 and so on. The true statement just doesn't cut it. I need the formula to be EXACT on item description but the quantity match to be closest to (but not greater than) to price breaks ONLY relevant to the part being evaluated.

My current index match on sheet1 is as follows - it only finds exact matches
{=INDEX('Sheet2'!$E$2:$E$8000,MATCH($N2&$T2,'Sheet2'!$A$2:$A$8000&'Sheet2'!$E$2:$E$8000,0))}

I've tried to be concise but if anything is unclear or you need clarity on anything, please let me know.

Thank you so much in advance for any efforts put into finding a solution.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
long formula alert


Book1
ABC
1Item A11
2Item A100£100
3Item A500£500
4Item A1000£1,000
5Item B50£20
6Item B100£40
7Item C100£1,000
8Item C1000£10,000
9Item C2000£20,000
10
11
12Item A751
13Item B7520
14Item C751000
15Item C150010000
Sheet1
Cell Formulas
RangeFormula
C12{=IF(B12$B$1:$B$9,$A$1:$A$9,A12),INDEX($C$1:$C$9,MATCH(A12&"/"&MINIFS($B$1:$B$9,$A$1:$A$9,A12),$A$1:$A$9&"/"&$B$1:$B$9,0)),LOOKUP(B12,IF($A$1:$A$9=A12,$B$1:$B$9),$C$1:$C$9))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Thanks for looking into this but I'm getting a #NAME ? result when applied correctly. The MINIFS command isn't working in isolation, perhaps because Item description is a text string and not numeric.

Thoughts?

Thanks
AJ
 
Upvote 0
ok, try this in C12 instead and copy down, again Entered with Ctrl+Shift+Enter.

Code:
=IF(B12 < MIN(IF($A$1:$A$9=A12,$B$1:$B$9)),INDEX($C$1:$C$9,MATCH(A12&"/"&MIN(IF($A$1:$A$9=A12,$B$1:$B$9)),$A$1:$A$9&"/"&$B$1:$B$9,0)),LOOKUP(B12,IF($A$1:$A$9=A12,$B$1:$B$9),$C$1:$C$9))
 
Last edited:
Upvote 0
warning:

the above only work if the Col B is in ascending order as per items in Col A as in your sample data
 
Upvote 0
That formula is a BEAST but...........it works a treat and is a spectacle to behold! Well done and thank you so much, you're a legend.

Have a drink on me!
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

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