how to display the column or cell the MIN() result came from?

[Fadi]

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

[bpeltzer]

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

"Fadi" wrote:

> 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

[Paul B]

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

=CELL("address",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 & 2003

"Fadi" <Fadi@discussions.microsoft.com> wrote in message
news:4D390AFC-8BE5-4093-A5A1-B12348EE2E10@microsoft.com...
> 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

[Sloth]

=INDIRECT("R1C"&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.

apple brown cat dog
a1x 1 2 3 4 apple
bv4 4 3 1 2 cat
5th 3 2 2 4 brown
tyu 2 1 3 3 brown
i97 4 3 2 1 dog


"Fadi" wrote:

> 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

[Fadi]

Thanks guys, it worked!