excel vlookup_Excel VLOOKUP在不同的范围

excel vlookup

You can use the VLOOKUP function to find data in a lookup table, based on a specific value. If you enter a product number in an order form, you can use a VLOOKUP formula to find the matching product name or price. See how to use Excel VLOOKUP in different ranges.

您可以使用VLOOKUP函数根据特定值在查找表中查找数据。 如果您在订购单中输入产品编号，则可以使用VLOOKUP公式查找匹配的产品名称或价格。 了解如何在不同范围内使用Excel VLOOKUP。

NOTE: The examples below use VLOOKUP to get the value from the correct table. You could do a similar lookup with the INDEX and MATCH functions.

注意 ：下面的示例使用VLOOKUP从正确的表中获取值。 您可以使用INDEX和MATCH函数进行类似的查找。

两个查询表 (Two Lookup Tables)

In some Excel workbooks, you might need to pull data from a specific table, depending on an option that the user has selected. For example, in the screen shot below, there are different rate tables for the East and West regions.

在某些Excel工作簿中，您可能需要从特定表中提取数据，具体取决于用户选择的选项。 例如，在下面的屏幕快照中，东部和西部地区有不同的费率表。

• Range B3:C6 is named Rates_East
  范围B3：C6命名为Rates_East
• Range E3:F6 is named Rates_West.
  范围E3：F6名为Rates_West。

创建VLOOKUP公式 (Create the VLOOKUP Formula)

On the data entry sheet, if East is entered in column A, then the VLOOKUP formula should use Rates_East as the lookup table.

在数据输入表上，如果在A列中输入East，则VLOOKUP公式应使用Rates_East作为查找表。

If West is entered as the region, the rate should come from the Rates_West table.

如果输入West作为区域，则汇率应来自Rates_West表。

If there are only a couple of lookup tables, you could use an IF function to select the correct table in the VLOOKUP.

如果只有几个查找表，则可以使用IF函数在VLOOKUP中选择正确的表。

=VLOOKUP(B3,IF(A3="East",Rates_East,Rates_West),2,0)

= VLOOKUP(B3，IF(A3 =“ East”，Rates_East，Rates_West)，2,0)

This solution could work in this example, where there are only two rate tables.

此解决方案可以在只有两个费率表的此示例中工作。

选择正确的查找表 (Select the Correct Lookup Table)

For situations when there are multiple lookup tables, an IF function probably wouldn't be practical.

对于有多个查找表的情况，IF函数可能不切实际。

Instead, you can use the INDIRECT function to return the correct lookup range.

而是可以使用INDIRECT函数返回正确的查找范围。

=VLOOKUP(B3,INDIRECT("Rates_" & A3),2,0)

= VLOOKUP(B3，INDIRECT(“ Rates_”＆A3)，2,0)

The INDIRECT function combines the text string "Rates_" with the region entered in column A, and returns the range with that name.

INDIRECT函数将文本字符串“ Rates_”与在A列中输入的区域组合在一起，并返回具有该名称的范围。

In the screen shot above, "West" was entered as the Region in cell A3.  The VLOOKUP formula will use Rates_West as the lookup table, and return the value for Group C in that table.

在上面的屏幕截图中，在单元格A3中输入了“ West”作为Region。 VLOOKUP公式将使用Rates_West作为查找表，并返回该表中C组的值。

Using the INDIRECT function with named ranges makes the VLOOKUP formula very flexible, and you could use any number of lookup tables in the workbook. ______________

将INDIRECT函数与命名范围一起使用将使VLOOKUP公式非常灵活，并且您可以在工作簿中使用任意数量的查找表。 ______________

翻译自: https://contexturesblog.com/archives/2010/05/21/excel-vlookup-in-different-ranges/

excel vlookup