Finding a value and returning corresponding info

[Vicky]

I am sure this is something simple, I am just not finding the best solution.
I have a list of data in Excel (2007 but I dont think that matters) The data
has header row (ABC, XYZ, etc) and data listed below. To the right of the
table I created the MIN fx to find the lowest cost provider of products. Now
I would like to have another column that indicated to me who the lowest cost
provider is. For instance the first row would be CAT. The one issue is
what if there are two providers with the same number.

I was thinmking of a lookup function to do this, but conditional formatting
is another option. Once I solve it for the first row, I want to copy the
formula for the subsequent rows.

Thanks!


ABC XYZ CAT POP
107 102 101 107 =MIN(A22)
209 201 203 201 =MIN(A33)
304 304 305 301 =MIN(A44)

 

[Pete_UK]

You haven't said what you would like to see if you have multiple
minima. In F3 do you want to see "XYZ, POP", and in F4 "ABC,
XYZ" (without the quotes), or something else?

How do you envisage the Conditional Formatting working in another
column? Surely this would highlight the number(s) in the columns which
are equal to the minimum value on each row, so no need for a sixth
column.

Hope this helps.

Pete

 

[OssieMac]

Hi Vicky,

The following formula assumes.
Your columns start at column A (or column 1) because the column numbers are
returned by the Match function.
Your low number is in column E because that number is used in the Match
function.
Your provider column is column F.

The formula below is in cell F2. (Can copy down)

=INDIRECT("R1" & "C"&MATCH(E2,A22,0),FALSE)

 

[OssieMac]

Hi again Vicky,

I have not handled the following.
"The one issue is what if there are two providers with the same number."

I don't know how to list multiple providers but you could use another column
and count the minimum values in the range like the following.
=COUNTIF(A22,E2)

Another suggestion is to use conditional formatting and highlight the
multiple minimum values in each row with a background (interior) color. To do
this:-
Select the range of values (A24 in your example).

Select Conditional format and formula (Depends on your version of xl for
exact instructions for this.)
Enter the following formula.
=AND(A2=$E2,COUNTIF($A2:$D2,$E2)>1)
Set the required format

Note when setting conditional format you set the formula as if it applies to
only the first cell of the selection and xl looks after applying correct
formula to the rest. However, you do need to think about the absolute
addressing. In this case it is required on the columns but not on the rows.