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

Select Minimum value and return the name of the supplier
 

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

Select Minimum value and return the name of the supplier
 

That's excellent. Thanks so much.

Cheers

Mark.


"Toppers" wrote in message
...
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

Select Minimum value and return the name of the supplier
 

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" wrote in message
...
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

Select Minimum value and return the name of the supplier
 

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" wrote in message
...
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" wrote in message
...
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

Select Minimum value and return the name of the supplier
 

Mark,

Enter this an array formula:

=INDEX($B$1:$E$1,1,MATCH(MIN(IF($B2:$E20,$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" wrote in message
...
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" wrote in message
...
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

Select Minimum value and return the name of the supplier
 

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" wrote in message
...
Mark,

Enter this an array formula:

=INDEX($B$1:$E$1,1,MATCH(MIN(IF($B2:$E20,$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" wrote in message
...
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" wrote in message
...
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 =----

Select Minimum value and return the name of the supplier
 

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:$G20,$B2:$G 2),1),$B2:$G2,0))

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

=INDEX($B$1:$G$1,1,MATCH(SMALL(IF($B2:$G20,$B2:$G 2),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" wrote in message
...
Mark,

Enter this an array formula:

=INDEX($B$1:$E$1,1,MATCH(MIN(IF($B2:$E20,$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" wrote in message
...
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" wrote in message
...
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 =----