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.
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
3,970
Office Version
365, 2019, 2016
Platform
Windows
long formula alert

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Item A </td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Item A </td><td style="text-align: right;;">100</td><td style="text-align: right;;">£100</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Item A </td><td style="text-align: right;;">500</td><td style="text-align: right;;">£500</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Item A </td><td style="text-align: right;;">1000</td><td style="text-align: right;;">£1,000</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Item B </td><td style="text-align: right;;">50</td><td style="text-align: right;;">£20</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Item B </td><td style="text-align: right;;">100</td><td style="text-align: right;;">£40</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Item C </td><td style="text-align: right;;">100</td><td style="text-align: right;;">£1,000</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Item C </td><td style="text-align: right;;">1000</td><td style="text-align: right;;">£10,000</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Item C </td><td style="text-align: right;;">2000</td><td style="text-align: right;;">£20,000</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Item A </td><td style="text-align: right;;">75</td><td style="text-align: right;background-color: #E2EFDA;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Item B </td><td style="text-align: right;;">75</td><td style="text-align: right;background-color: #E2EFDA;;">20</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">Item C </td><td style="text-align: right;;">75</td><td style="text-align: right;background-color: #E2EFDA;;">1000</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">Item C </td><td style="text-align: right;;">1500</td><td style="text-align: right;background-color: #E2EFDA;;">10000</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C12</th><td style="text-align:left">{=IF(<font color="Blue">B12<MINIFS(<font color="Red">$B$1:$B$9,$A$1:$A$9,A12</font>),INDEX(<font color="Red">$C$1:$C$9,MATCH(<font color="Green">A12&"/"&MINIFS(<font color="Purple">$B$1:$B$9,$A$1:$A$9,A12</font>),$A$1:$A$9&"/"&$B$1:$B$9,0</font>)</font>),LOOKUP(<font color="Red">B12,IF(<font color="Green">$A$1:$A$9=A12,$B$1:$B$9</font>),$C$1:$C$9</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

MR_J3NKO

New Member
Joined
Dec 18, 2018
Messages
6
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

Well-known Member
Joined
Oct 30, 2014
Messages
3,970
Office Version
365, 2019, 2016
Platform
Windows
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:

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
3,970
Office Version
365, 2019, 2016
Platform
Windows
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

New Member
Joined
Dec 18, 2018
Messages
6
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

Well-known Member
Joined
Oct 30, 2014
Messages
3,970
Office Version
365, 2019, 2016
Platform
Windows
you're welcome
 

Forum statistics

Threads
1,082,589
Messages
5,366,494
Members
400,896
Latest member
Scab

Some videos you may like

This Week's Hot Topics

Top