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?
kgerbChief EngineerCommented:
This formula will work I think


Open in new window

See attached workbook

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?
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.
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


regards, barry
kgerbChief EngineerCommented:
Writing formulas on your phone...Nice :-)
Maybe if your items are in col A and competitors in cols B-E, this?

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?

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.