Look up based on lowest value

[andrewzzz]

Hi,

I've attached the file that I need help with. Range A1:F11 I have a table with list of products and quantity per each location.

In J2 I have a criteria from the product list. In my example I entered Product7. I need a formula to enter in cell K2 si that it displays the location name with the lowest quantity for Product7. In my case it is location RR3. Next in L2 I need to display the quantity of Product7 for location RR3. So in my case it is 7. Same thing for the next cells M2:P2 in ascending order.

In my next row J3, the story is the same, it's just that I've entered Product4 as criteria.

So basically I've entered manually the outcome (marked red) so that it is understandable what I need.

I appreciate your help

[SlipEternal]

The easiest way to do it would be with helper columns. See attached?

The only problem with this method is if two locations have the same quantity, it will display the leftmost one multiple times.

[JohnTopley]

Try

in L2

=IFERROR(SMALL(INDEX($B$2:$F$11,MATCH($J2,$A$2:$A$11,0),0),1),"")

in K2

=IFERROR(INDEX($B$1:$F$1,,MATCH(L2,INDEX($B$2:$F$11,MATCH($J2,$A$2:$A$11,0),0),0)),"")

Copy L2 to N2 & P2 changing 1 to 2 & 3

Copy K2 to M2 and O2

[andrewzzz]

The easiest way to do it would be with helper columns. See attached?

The only problem with this method is if two locations have the same quantity, it will display the leftmost one multiple times.

SlipEternal, this is brilliant idea, thank you! There's only one thing that needs to be added. It may happen that a product is stored in 2 different location but the quantity is the same. Specifically, in your table, if I enter in cell C2 the value "3", then it will show twice location RR1, instead of RR1, RR2 and then RR4. Can you help please

[andrewzzz]

Try

in L2

=IFERROR(SMALL(INDEX($B$2:$F$11,MATCH($J2,$A$2:$A$11,0),0),1),"")

in K2

=IFERROR(INDEX($B$1:$F$1,,MATCH(L2,INDEX($B$2:$F$11,MATCH($J2,$A$2:$A$11,0),0),0)),"")

Copy L2 to N2 7 P2 changing 1 to 2 & 3

Copy K2 to M2 and O2

JohnTopley, thank you very nice of you. However as I mentioned above similar to SlipEternal example, it may happen that a product is stored two different locations with the same quantity, so if I enter in cell B8 value "7", then RR1 will show up twice. Can you help fixing that please?

[JohnTopley]

See attached with Helper columns:

in G2

=B2+COUNTIF($B$2:B2,B2)*COLUMN($A:A)*10^-2

copy across and down

in O2

=IFERROR(SMALL(INDEX($G$2:$K$11,MATCH($M2,$A$2:$A$11,0),0),1),"")

Cells formatted as NUMBER with zero decimal places

in N2

=IFERROR(INDEX($B$1:$F$1,,MATCH(O2,INDEX($G$2:$K$11,MATCH($M2,$A$2:$A$11,0),0),0)),"")

[JohnTopley]

Correction:

Change formula in G2 to

=IF(B2="","",B2+(COUNTIF($B$2:B2,B2)-1)*COLUMN($A:A)*10^-2)

so blank cells remain blank

[andrewzzz]

Correction:

Change formula in G2 to

=IF(B2="","",B2+(COUNTIF($B$2:B2,B2)-1)*COLUMN($A:A)*10^-2)

so blank cells remain blank

John thanks a lot for your effort. The problem is that I have over 200 locations and 7000 products, and creating a copy of these locations with formula would load the excel file. Your initial formula was perfect, apart from it not being able to deal with similar quantity. Can that formula be fixed in a different way without helper column?

[JohnTopley]

I am not aware of any method [the most common (only?)] other than the one I used to differentiate the same values by adding a small amount to each value.

The "issue" is the number of locations, requiring 200+ helper columns which could be put on a separate sheet or "hidden" in the same sheet.

However, these need only to be set up once and the (single) helper formula is only copied across and down.

So a little "one off" pain: I am sure you can copy/paste both the product list/locations for the helper column/row headings.

All you have to do is copy the equivalent of the "table" in B2:F11, insert the helper formula and job done.

You will have to create the "results" table but even that is only 6 formulae which can be dragged down columns.

[JohnTopley]

Another option is to use VBA: if this is acceptable, PM a moderator and ask to transfer this thread to the VBA/Macro thread.

Be sure to post a workbook which exactly represents the layout/format of your "real" workbook.

[andrewzzz]

I just tried to do that, but due tp high volume it simply "ran out of resources" and shut down. What about the method mentioned by SlipEternal, can it be corrected?

[SlipEternal]

Try this? It still uses helper columns, but not one for each location. It only uses the six helper columns that I used initially. It requires that each location name be distinct. And this same method could probably be used by JohnTopley's method since it uses dynamic arrays. The helper column was just easier for me.

[JohnTopley]

SlipEternal's solution looks good so go with that.

My original could be adapted to apply similar logic but as a solution has been provided there seems little merit in trying to change it.

[andrewzzz]

Try this? It still uses helper columns, but not one for each location. It only uses the six helper columns that I used initially. It requires that each location name be distinct. And this same method could probably be used by JohnTopley's method since it uses dynamic arrays. The helper column was just easier for me.

SlipEternal, what exactly is the error in my formula that doesn't allow me to enter it in cell GU2?
=IFERROR(SMALL(Table1[@[A1]:[A200]],1),"")
Can you check attachment please?

JohnTopley, I like your formula as well so if you can adapt it I see big merit here

[SlipEternal]

SlipEternal, what exactly is the error in my formula that doesn't allow me to enter it in cell GU2?
=IFERROR(SMALL(Table1[@[A1]:[A200]],1),"")
Can you check attachment please?

JohnTopley, I like your formula as well so if you can adapt it I see big merit here

I think this will do it. This uses JohnTopley's formulas and adds dynamic ranges to them, as well. I also used a better syntax for the dynamic ranges so I think they are easier to read.

[SlipEternal]

SlipEternal, what exactly is the error in my formula that doesn't allow me to enter it in cell GU2?
=IFERROR(SMALL(Table1[@[A1]:[A200]],1),"")
Can you check attachment please?

Also, Table1 does not exist in your workbook. Use Table5 instead.

[JohnTopley]

@SlipEternal: Nice one!

[andrewzzz]

you both are two crazy excel gurus, you prove that excel has no limits! Big thanks!

[JohnTopley]

Thank you the feedback and rep.

Not for the first time, I learned a different approach to a problem (from SlipEternal's solution).

[SlipEternal]

Sorry, I have not been on the forums since I posted my last reply. Per your PM, you asked that I make sure that it does not include locations that have a quantity of zero. This should do it. To get faster results, please post in the original thread or start a new one rather than a PM.