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

[MR_J3NKO]

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.

 

[AlanY]

long formula alert

Book1
	A	B	C
1	Item A	1	1
2	Item A	100	£100
3	Item A	500	£500
4	Item A	1000	£1,000
5	Item B	50	£20
6	Item B	100	£40
7	Item C	100	£1,000
8	Item C	1000	£10,000
9	Item C	2000	£20,000
10
11
12	Item A	75	1
13	Item B	75	20
14	Item C	75	1000
15	Item C	1500	10000
Sheet1

Cell Formulas
Range	Formula
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.

 

[MR_J3NKO]

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

 

[AlanY]

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

=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))

 

[AlanY]

warning:

the above only work if the Col B is in ascending order as per items in Col A as in your sample data

 

[MR_J3NKO]

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!

 

[AlanY]

you're welcome