Select Minimum value and return the name of the supplier

[Mark McDonough]

I'm having a real problem with this lookup. It is best to illustrate as
follows:

Site Supplier A Supplier B Supplier C Min

Bilo1 175000 150000 125000
=min(b2:d2)
Bilo2 125000 50000 60000
=min(b3:d3)
Bilo3 50000 60000 70000
=min(b4:b4)

and so on.

What I'm trying to do is look up the minimum value (in the horizontal range)
and return the name of the supplier. So in the first line of data, 125000 is
the minimum and the name of the supplier therefore is Supplier C (required
result) but I cannot get a hlookup to work. I have 24 suppliers and 500+
sites.

This formula I will then copy down column F so I can then sort out which
suppliers are cheapest at each site.

Any help much appreciated.

Mark

 

[Guest]

Try:

=INDEX($B$1:$D$1,1,MATCH(MIN($B2:$D2),$B2:$D2,0))

Change ranges to suit your needs

A B C D E F
Supplier 1 Supplier 2 Supplier 3 Min
Bilo1 175 150 125 Supplier 3
Bilo2 125 50 60 Supplier 2
Bilo3 50 60 70 Supplier 1

HTH

 

[Mark McDonough]

That's excellent. Thanks so much.

Cheers

Mark.

 

[Mark McDonough]

Thanks very much for this formula. I tried it in my file where my range of
data is columns DY to FD but for some reason, the name of the cheapest
supplier in the first row of data is repeated in all other rows.

I haven't used these functions before and I've analysed the formula in each
row to check that absolute values are as they should be and they are so I
don't know where I'm going wrong.

In another part of the spreadsheet there is a minimum value calculation on
each row that is entered as an array formula. Does that affect the results
I'm getting or not? Anyone know.

 

[Mark McDonough]

Having a closer look at this data, I should probably say that many of the
columns of data, some suppliers have chosen not to provide a quote so I
think that may be playing havoc with the minimum formula.

In another part of the spreadsheet, the minimum is obtained by doing the
following:

=if(min(DY2:FD2)=0,"",min(DY2:FD2)) entered with Control Shift Enter.

Does that provide any clarity?

 

[Guest]

Mark,

Enter this an array formula:

=INDEX($B$1:$E$1,1,MATCH(MIN(IF($B2:$E2>0,$B2:$E2)),$B2:$E2,0))

Change ranges to suit.

FYI: in XL2003, MIN ignores BLANK cells and my original formula worked OK on
my testing.

HTH

 

[Mark McDonough]

Further on this, how would I find out what the next minimum quote is and the
next after that so I have the 3 cheapest suppliers?

 

[toppers]

Enter as array formula for 1st, 2nd and 3rd smallest ...:

MIN formula can be replaced by this first formula below:

=INDEX($B$1:$G$1,1,MATCH(SMALL(IF($B2:$G2>0,$B2:$G2),1),$B2:$G2,0))

=INDEX($B$1:$G$1,1,MATCH(SMALL(IF($B2:$G2>0,$B2:$G2),2),$B2:$G2,0))

=INDEX($B$1:$G$1,1,MATCH(SMALL(IF($B2:$G2>0,$B2:$G2),3),$B2:$G2,0))

You are testing my formula knowledge to its limit but the above works
OK!!.

Let me know if this works OK for you.

HTH