Select Minimum value and return the name of the supplier

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

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" wrote:

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

That's excellent. Thanks so much.

Cheers

Mark.


"Toppers" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
>> 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
>>
>>
>>

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.


"Toppers" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
>> 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
>>
>>
>>

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?


"Mark McDonough" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>
> "Toppers" <[email protected]> wrote in message
> news:[email protected]...
>> 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" wrote:
>>
>>> 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
>>>
>>>
>>>
>
>

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" wrote:

> 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?
>
>
> "Mark McDonough" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
> >
> > "Toppers" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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" wrote:
> >>
> >>> 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
> >>>
> >>>
> >>>
> >
> >
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
>> 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?
>>
>>
>> "Mark McDonough" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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.
>> >
>> >
>> > "Toppers" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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" wrote:
>> >>
>> >>> 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
>> >>>
>> >>>
>> >>>
>> >
>> >
>>
>>
>>



----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----

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

Mark McDonough wrote:
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" wrote:
> >
> >> 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?
> >>
> >>
> >> "Mark McDonough" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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.
> >> >
> >> >
> >> > "Toppers" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> 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" wrote:
> >> >>
> >> >>> 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
> >> >>>
> >> >>>
> >> >>>
> >> >
> >> >
> >>
> >>
> >>
>
>
>
> ----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
> http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
> ----= East and West-Coast Server Farms - Total Privacy via Encryption =----