Comparing cells and output text

[Rocketeer]

Hi!

I'm comparing prices of different companies and products.
If I have prices in different cells for each company I can
calculate the lowest price using:

=MIN(A1;B1;C1;D1)

The output is written in E1. How will I have cell F1 write
the company name? Let's say the companies are IBM,
MS, DELL, HP.

If MS has the lowest price I will have the value of B1 in
cell E1 and in cell F1 I will have the text MS.

It is simple to to with an IF statement when you only have
two companies. But what if you have three or more? Is there
a formula to use for a general case with many companies?

I am also curious if it it possible to change the color of cell
F1 depending on the text there? Let's say blue for MS, red
for Dell etc.

Thanks!

 

[Gord Dibben]

To return the Company names to column F try the VLOOKUP function.

Debra Dalgleish has goo instructions and a downloadable workbook.

http://www.contextures.on.ca/xlFunctions02.html

For changing cells colors based on cell value........text or numeric see
Conditional Formatting

See also Debra's site at http://www.contextures.on.ca/xlCondFormat01.html for
more on CF


Gord Dibben MS Excel MVP

 

[Max]

Q1:
This might suffice
In E1: =INDEX({"IBM","MS","DELL","HP"},MATCH(MIN(A11),A11,0))

Q2:
You could use CF for col F, but only up to 3 colours (in xl2003)
Steps would go something like this ..
Select col F (F1 active), click Format > Conditional Formatting
Condition 1
Formula Is: =$E1="MS"
Format to taste

Condition 2
Formula Is: =$E1="DELL"
Format to taste

Condition 3
Formula Is: =$E1="IBM"
Format to taste
OK out

 

[Rocketeer]

Thanks for the links. Lots of useful information.

/Peter

 

[Rocketeer]

Hi Max!

I can see were you want to go with the formulas. I'm having trouble
with the MATCH function. Lets say I don't have data in A11 but
rather in F2, J2, N2 and R2.

=MATCH(MIN(F2;J2;N2;R2);(F2;J2;N2;R2);0)

But this outputs a #N/A in the target cell V2. Do you have any
suggestions?

Thanks!

 

[Max]

You need to pull it into a contiguous range,
eg using simple link formulas such as these:

in S2: =IF(F2="","",F2)
in T2: =IF(J2="","",J2)
in U2: =IF(N2="","",N2)
in V2: =IF(R2="","",R2)

Then, you could place in W2:
=INDEX({"IBM","MS","DELL","HP"},MATCH(MIN(S2:V2),S2:V2,0))

Note that in the event of any ties in the minimum values,
W2 will return only the 1st match from the left

(amend the commas to semicolons to suit your settings)

 

[Rocketeer]

Hi!

Now it works like a charm. Thanks for your help.

/Peter

 

[Max]

Glad it did. Welcome.
Do take a moment to press the "Yes" button below ...