# MIN function or similar in EXCEL 2007

Hi all.

I have a column with 5columns: ItemNumber, Competitor1, Competitor2, Competitor3, Competitor4

The competitor columns have the price for each item number. I want to add 2 more columns: LowestPrice and Competitor.

I want to be able to find the lowest price and who the competitor is.

I used the MIN function but that just gives me the lowest price but how do I find out who the competitor with the lowest price is.

Any ideas? Thanks.
###### Who is Participating?

Chief EngineerCommented:
This formula will work I think

=INDEX(\$G\$3:\$J\$3,MATCH(MIN(G4:J4),G4:J4,0))

See attached workbook

Kyle
Q-27393330-RevA.xlsx
0

Commented:
Are you doing this for each item? If you only have two items, couldn't you use an IF formula, e.g. IF A1<B1 "Competitor 1" etc?
0

Author Commented:
The file has about 90 items. So there will be items where we have pricing for all 4 competitors, other items will only have 1 or 2 competitor pricing. I want to be able to see the lowest price and the competitor column header. Each competitor is it's column all I would need is the column header.
0

Commented:
Out of just 2?

In D2 try

=IF(A2>B2,B\$1,IF(B2>A2,A\$1,"Tie"))

regards, barry
0

Chief EngineerCommented:
@Barry
Writing formulas on your phone...Nice :-)
0

Commented:
Maybe if your items are in col A and competitors in cols B-E, this?

=INDEX(\$B\$1:\$E\$1,MATCH(MIN(B2:E2),B2:E2,0))
0

Commented:

took me ten minutes to type that, too! I'm not really one of the "text generation"......

Why aren't the > < and = signs more accessible, isn't that what everybody wants to use?

barry
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.