Find Best Price With Excel INDEX and MATCH

If you’re setting up a new office, or going grocery shopping, you can use Excel to compare prices. With the Excel functions – MIN, INDEX and MATCH – you can calculate the lowest price for each item, and which store sells at that price.

Then, print a list, and go shopping!

BestPriceIndexMatch01

Calculate Best Price with MIN

In this example, we have done some research, and entered the item prices from three stores, in cells B2:D6.

The next step is to calculate the best price for each item, by using the Excel MIN function. The formula in cell E2 is:

=MIN(B2:D2)

BestPriceIndexMatch02

Then, copy the formula down to cell E6.

Find Store With Lowest Price MATCH

The final step, before printing your shopping list, is to determine which store has that best price. For this, you’ll use the INDEX and MATCH functions.

With the MATCH function, you can find the position of the lowest price, in the 3 columns with store prices. This MATCH formula looks in cells B2:D2, to find the best price from cell E2.

=MATCH(E2,B2:D2,0)

BestPriceIndexMatch03

Sears has the lowest price for a lamp, and it is the second store in our worksheet, so the MATCH result is 2.

Get the Store Name With INDEX

Our shopping trip will be easier if there is a store name in column F, Instead of a store position number. The store names are listed in cells B1:D1, and the INDEX function can return a value from a specific position in a range.

With the INDEX function, we could select cells B$1:D$1, and type a 2 as the column number that should be returned.

=INDEX(B$1:D$1,,2)

The reference to B$1:D$1 uses dollar signs before the row number, because we don’t want that reference to shift, as we copy the formula down, in column F.

BestPriceIndexMatch04

The result in cell F2 is Sears.

BestPriceIndexMatch05

Combine INDEX and MATCH

Instead of typing the column number in the INDEX formula, we can use our MATCH formula to calculate the column number.

Here is the combined INDEX and MATCH formula, in cell F2:

=INDEX(B$1:D$1,,MATCH(E2,B2:D2,0))

BestPriceIndexMatch06

Copy the formula down to F6, and you’re ready to hit the shopping centre!

Download the Sample Excel File

To see the formulas, and the sample data, you can download the Best Price with Excel INDEX and MATCH sample workbook. It is in Excel 2007 format, and zipped. There are no macros in the file.

Watch the Best Price with Excel INDEX and MATCH Video

To see the steps for finding the store with the lowest price, watch this Excel video tutorial.

Watch on YouTube: Best Price with Excel INDEX and MATCH

_____________

You may also like...

18 Responses

  1. Robin says:

    Link to sample file does not work.

  2. Thanks Robin, the link is fixed now.

  3. Jason Morin says:

    Hi Debra,

    Great example of using INDEX/MATCH. I shortened your formula just slightly:

    =INDEX(B$1:D$1,MATCH(E2,B2:D2,0))

    And as you know but didn’t state, if 2 or more stores share the lowest price for an item, the formula will pull the 1st store starting from the left.

  4. Thanks Jason, I was going to mention that, and completely forgot!

  5. SteveT says:

    Thanks this really helps with combining the 2 in my mind. I have been struggling with the concept and have only been able to make it work with trial and error. I will try and keep the framework you presented in my head.

    Thanks!

  6. Dante says:

    Hi, Link to sample data does not work.

  7. Thanks Dante, I’ve fixed the link, so please try again.

  8. Ashit Shroff says:

    Very useful. Thanks a lot.

  9. Praveen Gulati says:

    I must say, a big : Thank You !

  10. Mohammed says:

    Thanks a lot its very much useful to understand the concept of index. I’d would like to ask if the particular product sell by same price at this point how we can differentiate.

    There is option to know particular product have same price in two shops. Which one you need to go like that…………………

  11. Kamran Mumtaz says:

    We can use MIN function in this formula rather than take help from column E like

    =INDEX(B$1:D$1,MATCH(MIN(B2:D2),B2:D2,0))

  12. Martin says:

    Thank you. This helps a lot in my project!

  13. Micky says:

    I want to be able to see if there is a way to get the 2nd store to appear if they have the same price. Does anyone have suggestions?

  14. Jeff says:

    Great stuff, I was hoping you could answer a question.
    Using your example above say item lamp had a 0 value and you wanted to exclude that.
    I have actually combined the index, match and min.
    How could you had the if statement to the min function? thanks

  15. Ravine says:

    Many thanks for the excellent way you presented this tutorial. Instead of having column E and F , could you highlight the lowest price by colouring in the cell
    Thank you once again

  16. David says:

    Hi, I’m trying to find a formula that would help me solve the following problem:
    Code Country LE#
    123 US 4026
    123 UK 4026
    123 US 3026
    435 CN 1419
    435 CN 1398
    I need to find a formula that can return lowest LE# so if I look up 123 & US it should return with the lowest LE# which is 3026
    Thanks.

  17. Sudhir Lipare says:

    I want a program that will write Lowest 2nd lowest & 3rd lowest below vendor name in column. Can u help with this?

  18. Matt Fairman says:

    How would i exclude blank cells in the row for example if a particular store doesn’t sell that item so one or two cells in the row are left blank.
    The way ive got it at the moment is =MIN(G18:L18) returns a 0 because in my row i’ve got blank cells at I, J and L so its returning the lowest numeral 0.

Leave a Reply

Your email address will not be published. Required fields are marked *