使用Excel INDEX和MATCH查找最优惠的价格

If you're setting up a new office, or going grocery shopping, you can use Excel to compare prices. Find best price with Excel INDEX and MATCH functions, combined with MIN. This will help you calculate the lowest price for each item, and see which store sells at that price. Then, print a list, and go shopping!

如果您要开设新办公室或去杂货店购物，则可以使用Excel比较价格。 与MIN结合使用Excel INDEX和MATCH函数查找最佳价格。 这将帮助您计算每个商品的最低价格，并查看哪个商店以该价格出售。 然后，打印列表，然后购物！

用MIN计算最优惠的价格 (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.

在此示例中，我们进行了一些研究，并在单元格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:

下一步是使用Excel MIN函数计算每个项目的最佳价格。 E2单元格中的公式为：

=MIN(B2:D2)

= MIN(B2：D2)

Then, copy the formula down to cell E6.

然后，将该公式复制到单元格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.

在打印购物清单之前，最后一步是确定哪家商店的价格最优惠。 为此，您将使用INDEX和MATCH函数。

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功能，您可以在带有商店价格的3列中找到最低价格的头寸。 此MATCH公式在单元格B2：D2中查找，以从单元格E2中找到最佳价格。

=MATCH(E2,B2:D2,0)

= MATCH(E2，B2：D2,0)

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

Sears的灯泡价格最低，这是我们工作表中的第二家商店，因此MATCH结果为2。

使用INDEX获取商店名称 (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.

如果在F列中有商店名称而不是商店位置编号，我们的购物之旅将会更加轻松。 商店名称在单元格B1：D1中列出，并且INDEX函数可以从范围中的特定位置返回值。 使用INDEX函数，我们可以选择单元格B $ 1：D $ 1，然后键入2作为应返回的列号。

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

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

对B $ 1：D $ 1的引用在行号之前使用美元符号，因为我们不希望该引用移动，因为我们将公式向下复制到F列中。

The result in cell F2 is Sears.

F2单元格中的结果为Sears。

结合INDEX和MATCH (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.

不用在INDEX公式中键入列号，我们可以使用MATCH公式来计算列号。

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

这是单元格F2中的INDEX和MATCH组合公式：

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

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

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

将公式复制到F6，即可开始前往购物中心！

下载示例Excel文件 (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.

要查看公式和示例数据，可以下载Excel INDEX和MATCH示例工作簿的最佳价格 。 它是Excel 2007格式，并已压缩。 该文件中没有宏。

使用Excel INDEX和MATCH视频观看最优惠的价格 (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.

要查看找到价格最低的商店的步骤，请观看此Excel视频教程。

演示地址

翻译自: https://contexturesblog.com/archives/2011/06/20/find-best-price-with-excel-index-and-match/