Solved

# MIN function or similar in EXCEL 2007

Posted on 2011-10-12
Medium Priority
204 Views
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.
0
Question by:printmedia
• 2
• 2
• 2
• +1

LVL 24

Expert Comment

ID: 36957235
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 Comment

ID: 36957250
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

LVL 50

Expert Comment

ID: 36957251
Out of just 2?

In D2 try

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

regards, barry
0

LVL 12

Accepted Solution

kgerb earned 1000 total points
ID: 36957256
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

LVL 12

Expert Comment

ID: 36957270
@Barry
Writing formulas on your phone...Nice :-)
0

LVL 24

Assisted Solution

StephenJR earned 1000 total points
ID: 36957271
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

LVL 50

Expert Comment

ID: 36957843
>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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
###### Suggested Courses
Course of the Month15 days, 1 hour left to enroll

#### 839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.