vlookup match_具有VLOOKUP和MATCH功能的Excel价格表

vlookup match

You can create order forms and price lists in Excel, and automatically show a price when a product is selected in the order form. But what happens if you want to give some customers special pricing, or offer sales pricing occasionally? Here's how to customize your Excel price list with VLOOKUP and MATCH.

您可以在Excel中创建订单和价格清单，并在订单中选择产品时自动显示价格。 但是，如果您想给某些客户特殊定价，或偶尔提供销售定价，会发生什么呢？ 这是使用VLOOKUP和MATCH自定义Excel价格表的方法。

使用VLOOKUP功能 (Using the VLOOKUP Function)

With a simple VLOOKUP function in Excel, you can pull a product price from a lookup table. For example, if a customer orders a jacket, the price is 25, based on this lookup table. The lookup table is a named range, ProductLookup.

使用Excel中的简单VLOOKUP函数 ，您可以从查找表中提取产品价格。 例如，如果客户订购一件夹克，则基于此查找表的价格为25。 查找表是一个命名范围ProductLookup。

To show the price after a product is selected in an order form, use a VLOOKUP formula to find that product in the lookup table. For example:

要在订购表中选择产品后显示价格，请使用VLOOKUP公式在查找表中找到该产品。 例如：

=IF(B11="","",VLOOKUP(B11,ProductLookup,2,FALSE))

= IF(B11 =“”，“”，VLOOKUP(B11，ProductLookup，2，FALSE))

If cell B11 is empty (no product has been selected), the formula result is an empty string, and cell C11 will appear empty.

如果单元格B11为空(未选择任何产品)，则公式结果为空字符串，单元格C11将显示为空。

If a product has been selected in cell B11, the VLOOKUP formula finds the price in column 2 of the ProductLookup table.

如果在单元格B11中选择了某个产品，则VLOOKUP公式在ProductLookup表的第2列中查找价格。

选择一个可变列 (Select a Variable Column)

In the simple example shown above, the price will always come from the second column of the lookup table.

在上面显示的简单示例中，价格始终来自查询表的第二列。

To use variable pricing, you could create a lookup table with two or more columns of prices, instead of just one.

要使用可变定价，您可以创建一个包含两列或更多列价格而不是一列的查找表。

In this example, the ProductLookup table has four columns, instead of two - Product, Price, Promo and Sale.

在此示例中，ProductLookup表具有四列，而不是两列-产品，价格，促销和销售。

选择定价类型 (Select a Pricing Type)

On the order form, add a drop down list where you can select one of those pricing types – Price, Promo or Sale.

在订购表上，添加一个下拉列表，您可以在其中选择这些定价类型之一-价格，促销或销售。

更改VLOOKUP (Change the VLOOKUP)

Then, in the VLOOKUP formula, replace the column number with a MATCH formula.

然后，在VLOOKUP公式中，将列号替换为MATCH公式 。

=IF(B11="","",VLOOKUP(B11,ProductLookup,MATCH($E$4,Pricing,0)+1,FALSE))

= IF(B11 =“”，“”，VLOOKUP(B11，ProductLookup， MATCH($ E $ 4，Pricing，0)+1 ，FALSE))

The MATCH formula finds the pricing type selected in cell E4, in a range named Pricing (cells B1:D1 in the lookup table).

MATCH公式在名为Pricing的范围内(查找表中的单元格B1：D1)查找在单元格E4中选择的定价类型。

这个怎么运作 (How It Works)

If Promo is selected, the MATCH formula returns a 2, because Promo is the second item in the Pricing range.

如果选择了促销，则促销公式将返回2，因为促销是定价范围内的第二项。

You’ll add 1 to that number, because the ProductLookup table has one column to the left of the pricing columns.

您将在该数字上加1，因为ProductLookup表在定价列的左侧有一列。

So, 2 + 1 = 3, and the promo pricing will come from the 3rd column of the ProductLookup table.

因此，2 +1 = 3，促销价格将来自ProductLookup表的第3列。

观看VLOOKUP / MATCH视频 (Watch the VLOOKUP / MATCH Video)

To see another example of using MATCH with VLOOKUP, watch this short video.

要查看将MATCH与VLOOKUP结合使用的另一个示例，请观看此简短视频。

演示地址

翻译自: https://contexturesblog.com/archives/2010/02/08/excel-price-list-with-vlookup-and-match-function/

vlookup match