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.
printmediaAsked:
Who is Participating?
 
kgerbChief EngineerCommented:
This formula will work I think

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

Open in new window


See attached workbook

Kyle
Q-27393330-RevA.xlsx
0
 
StephenJRCommented:
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
 
printmediaAuthor 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
barry houdiniCommented:
Out of just 2?

In D2 try

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

regards, barry
0
 
kgerbChief EngineerCommented:
@Barry
Writing formulas on your phone...Nice :-)
0
 
StephenJRCommented:
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
 
barry houdiniCommented:
>Writing formulas on your phone.

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.

All Courses

From novice to tech pro — start learning today.