software

Hello,

I have an Excel sheet with product prices from diffrent suppliers; each
supplier has a column (with their company name in the first cel), each row
represents a price for a product. There are many suppliers and many products
(about 20 columns and 200 rows).

I need to find out which supplier has the lowest price for each product, so
in the last column, I am using the Min() function to determine the lowest
price per row; BUT I need a way to display which cell or better yet which
column or supplier the Min() result came from.

Can anyone help me out please?

Thanks

=match(min(4:4),4:4,false) would tell you the first column (by number) that
has the smallest value in row 4.

quot;Fadiquot; wrote:

gt; Hello,
gt;
gt; I have an Excel sheet with product prices from diffrent suppliers; each
gt; supplier has a column (with their company name in the first cel), each row
gt; represents a price for a product. There are many suppliers and many products
gt; (about 20 columns and 200 rows).
gt;
gt; I need to find out which supplier has the lowest price for each product, so
gt; in the last column, I am using the Min() function to determine the lowest
gt; price per row; BUT I need a way to display which cell or better yet which
gt; column or supplier the Min() result came from.
gt;
gt; Can anyone help me out please?
gt;
gt; Thanks

Fadi, here is one way, change range to your range, if two valves are the min
will show the first one

=CELL(quot;addressquot;,INDEX(A2:J2,MATCH(MIN(A2:J2),A2:J2 ,0)))

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 amp; 2003

quot;Fadiquot; gt; wrote in message
...
gt; Hello,
gt;
gt; I have an Excel sheet with product prices from diffrent suppliers; each
gt; supplier has a column (with their company name in the first cel), each row
gt; represents a price for a product. There are many suppliers and many
gt; products
gt; (about 20 columns and 200 rows).
gt;
gt; I need to find out which supplier has the lowest price for each product,
gt; so
gt; in the last column, I am using the Min() function to determine the lowest
gt; price per row; BUT I need a way to display which cell or better yet which
gt; column or supplier the Min() result came from.
gt;
gt; Can anyone help me out please?
gt;
gt; Thanks
=INDIRECT(quot;R1Cquot;amp;MATCH(MIN(B2:E2),B2:E2,0) 1,FALSE)

here is a formula for the first line in the following example. Adjust to
encompasses all of your data, and then copy it down. You will notice if two
are equal it returns the first company in the list.

applebrowncatdog
a1x1234apple
bv44312cat
5th3224brown
tyu2133brown
i974321dogquot;Fadiquot; wrote:

gt; Hello,
gt;
gt; I have an Excel sheet with product prices from diffrent suppliers; each
gt; supplier has a column (with their company name in the first cel), each row
gt; represents a price for a product. There are many suppliers and many products
gt; (about 20 columns and 200 rows).
gt;
gt; I need to find out which supplier has the lowest price for each product, so
gt; in the last column, I am using the Min() function to determine the lowest
gt; price per row; BUT I need a way to display which cell or better yet which
gt; column or supplier the Min() result came from.
gt;
gt; Can anyone help me out please?
gt;
gt; Thanks

Thanks guys, it worked!