合并Excel VLOOKUP的值

Do vampires prefer a specific blood type? Type A? Type B? Type AB? Are you positive? During the holidays, they might drink glögg, or Cosmopolitans! Anyway, Marsha probably isn't a vampire, but she wants to choose A or AB when doing a VLOOKUP. Here's how you can combine values for Excel VLOOKUP formulas.

吸血鬼喜欢特定的血型吗？ A型？ B型？ AB型？ 你积极吗？ 在假期期间，他们可能会喝glögg或世界主义者！ 无论如何，Marsha可能不是吸血鬼，但她想在进行VLOOKUP时选择A或AB 。 这是如何组合Excel VLOOKUP公式的值的方法。

Here's what Marsha needs to do:

这是Marsha需要做的：

• If there is only a value in column A, use A for the VLOOKUP
  如果列A中只有一个值，则对VLOOKUP使用A
• If there is also a value in column B, use A & B for the VLOOKUP
  如果B列中还有一个值，请对VLOOKUP使用A和B

数据布局 (The Data Layout)

I don't have a copy of Marsha's Excel file, but her lookup table, with Parts information, might look something like the screen shot below.

我没有Marsha的Excel文件的副本，但是她的查找表以及部件信息可能看起来像下面的屏幕快照。

In column A, there's a 3 letter code for each part, and some codes have a number at the end, to indicate length.

在A列中，每个部分都有一个3个字母的代码，有些代码的末尾有一个数字，以指示长度。

The lookup table is named PriceLU, as you can see in the Name Box, at the top left of the screen shot.

该查询表名为PriceLU，如您在屏幕快照左上方的“名称框”中所见。

输入订单 (Enter an Order)

When customers enter an order, they put in a part code, and sometimes they enter a part length.

客户输入订单时，他们会输入零件代码，有时会输入零件长度。

In columns C and D, a VLOOKUP formula can pull the description and price from the PriceLU table.

在C和D列中，VLOOKUP公式可以从PriceLU表中提取描述和价格。

单个VLOOKUP值 (Single VLOOKUP Value)

For a normal VLOOKUP formula, you'd refer to a single cell as the lookup value.

对于普通的VLOOKUP公式，您可以将单个单元格称为查找值。

For example, the following formula would get the price for a wrench (WRN) from the price lookup table:

例如，以下公式将从价格查询表中获得扳手(WRN)的价格：

=VLOOKUP($A3,PriceLU,3,FALSE)

= VLOOKUP($ A3，PriceLU，3，FALSE)

合并VLOOKUP值 (Combine the VLOOKUP Values)

However, if there is a length in column B on the Orders sheet, you need to include that as part of the lookup value.

但是，如果“订单”表上的B列中有一个长度，则需要将该长度作为查找值的一部分。

In the first argument of the VLOOKUP formula, you can check for a value in column B. If there's something in that cell, show a hyphen and the value in column B.

在VLOOKUP公式的第一个参数中，可以检查B列中的值。如果该单元格中有内容，请在B列中显示连字符和值。

IF($B2,"-","") & $B2

IF($ B2，“-”，“”)＆$ B2

Here's the revised VLOOKUP formula, that combines the values in columns A and B, when necessary, to get the price, from column 3 in the lookup table:

这是修改后的VLOOKUP公式，该公式在必要时结合A和B列中的值以从查找表的第3列中获取价格：

=VLOOKUP($A2 & IF($B2,"-","") & $B2,PriceLU,3,FALSE)

= VLOOKUP($ A2＆IF($ B2，“-”，“”)＆$ B2，PriceLU，3，FALSE)

The same VLOOKUP formula is used in column C, with the lookup column changed to 2, to get the Part description from the lookup table.

在列C中使用相同的VLOOKUP公式，将查找列更改为2 ，以从查找表中获取零件描述。

=VLOOKUP($A2 & IF($B2,"-","") & $B2,PriceLU, 2, FALSE)

= VLOOKUP($ A2＆IF($ B2，“-”，“”)＆$ B2，PriceLU， 2 ，FALSE)

组合VLOOKUP值的更多方法 (More Ways to Combine VLOOKUP Values)

You can visit the VLOOKUP page on the Contextures website, to see more ways to Combine VLOOKUP Values. And keep an eye out for vampires! __________

您可以访问Contextures网站上的VLOOKUP页面，以查看组合VLOOKUP值的更多方法。 并留意吸血鬼！ __________

翻译自: https://contexturesblog.com/archives/2010/12/24/vlookup-vampires/