This works very well.....Thank you.
Just trying to understand the formula though. I have a column of minimum
data so that I can probably get by with a simpler formula.
What is the COLUMN(A:A) and SMALL for. It seems to me to be redundant. The
formula I have used is exactly as presented below but the whole calculation
does not depend on column A at all.
Having used this formula, a major hurdle has been overcome at work and now
they want me to present it to the group - quite scared!!!
"Mark McDonough" wrote in message
...
Thanks very much guys.........I'll give it a go at work tomorrow
"Roger Govier" wrote in message
...
Hi Mark
Try the array formula (Enter with Control+Shift+Enter)
{=INDEX($B$1:$E$1,1,MATCH(SMALL(IF($B2:$E20,$B2:$ E2),COLUMN(A:A)),$B2:$E2,0))}
Copy across through successive columns.
Column(A:A) will find the first smallest. As you drag across that will
change to B:B etc to find the second smallest etc.
If there is no value that meets the test, for example if there are only 2
values out of the 3 entered, then it can't find the third smallest and
will return a #NUM error.
If you want to get rid of this, then wrap the whole formula in an error
trap
=IF(ISERROR(formula),"",formula)
--
Regards
Roger Govier
"Mark McDonough" wrote in message
...
Following up on a previous query..........I am able to look along a row
of values and return the name of the cheapest supplier thanks muchly
toJohn Topely and Bob Philips.
To obtain the name of the supplier I tried
=INDEX($B$1:$E$1,1,MATCH(MIN(IF($B2:$E20,$B2:$E2) ),$B2:$E2,0))
as suggested and it works well.
Extending on that idea, how would I go about returning the name of the
supplier that had the next cheapest rate and the third cheapest given
that I have 24 suppliers. For the purpose of illustration, I have only
shown 3 in my example.
Site Supplier A Supplier B Supplier C
Bilo1 175000 150000 125000
Bilo2 125000 50000 60000
Bilo3 50000 60000 70000
Any help appreciated
Cheers
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
=----
----== 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
=----
----== 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 =----