MIN function or similar in EXCEL 2007

Posted on 2011-10-12
Last Modified: 2012-05-12
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.
Question by:printmedia
    LVL 24

    Expert Comment

    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?

    Author Comment

    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.
    LVL 50

    Expert Comment

    by:barry houdini
    Out of just 2?

    In D2 try


    regards, barry
    LVL 12

    Accepted Solution

    This formula will work I think


    Open in new window

    See attached workbook

    LVL 12

    Expert Comment

    Writing formulas on your phone...Nice :-)
    LVL 24

    Assisted Solution

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

    LVL 50

    Expert Comment

    by:barry houdini
    >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?


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

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

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

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now