If you need to get a product price in Excel, you can use VLOOKUP or INDEX/MATCH to get the price from a lookup table, based on a product code. But what if you have two pieces of information, such as a product name, and a size, and you want to find the price based on that information? How can you do an Excel lookup with two criteria?
如果需要在Excel中获得产品价格，则可以使用VLOOKUP或INDEX / MATCH根据产品代码从查找表中获得价格。 但是，如果您有两条信息(例如产品名称和尺寸)，并希望根据该信息找到价格，该怎么办？ 如何使用两个条件进行Excel查找？
(Use INDEX and MATCH)
A few years ago, I posted an example that shows how to check multiple criteria with INDEX and MATCH. There is a lookup table with columns for product code, item name, item size and the price.
几年前，我发布了一个示例，展示了如何使用INDEX和MATCH检查多个条件 。 有一个查找表，其中包含产品代码，商品名称，商品尺寸和价格的列。
Below the table, I entered "Jacket" as the item name and "Large" as the size. In cell E13, I need to find the price for that item and size.
在表格下方，我输入了“夹克”作为商品名称，并输入了“大”作为尺寸。 在单元格E13中，我需要找到该商品和尺寸的价格。
(Find the Row That Matches)
To find the price, you have to check each row, with two tests:
要查找价格，您必须检查每一行，并进行两个测试：
- Is the item name the same as the item in cell C13? (TRUE or FALSE)
- Is the item size the same as the size in cell D13? (TRUE or FALSE)
If you multiply the result of those two tests in each row, the result is a 1 or a 0. The row with 2 TRUE results has a 1, and all other rows, with one or two FALSE results, show a zero.
如果在每行中将这两个测试的结果相乘，则结果为1或0。具有2个TRUE结果的行具有1，而具有一个或两个FALSE结果的所有其他行均显示为零。
With those formulas on the worksheet, you could use the MATCH function to find the 1 in column H, and return the price from that row.
使用工作表上的那些公式，您可以使用MATCH函数在H列中找到1，然后从该行返回价格。
(Do the Tests in a Formula)
Instead of adding extra columns to the worksheet, you can use an array-entered formula to do the tests, and the multiplication.
您可以使用输入数组的公式来执行测试和乘法，而不是向工作表中添加额外的列。
Here is the INDEX/MATCH formula that for this example.
这是此示例的INDEX / MATCH公式。
=INDEX(E2:E10,MATCH(1,(C13=C2:C10)*(D13=D2:D10),0))
= INDEX(E2：E10，MATCH(1，(C13 = C2：C10)*(D13 = D2：D10)，0))
NOTE: This is an array-entered formula, so press Ctrl + Shift + Enter, instead of just pressing the Enter key.
注意：这是一个输入数组的公式，因此请按Ctrl + Shift + Enter ，而不仅仅是按Enter键。
The MATCH function will find the 1 in that array of results, and the formula returns the price in that row.
MATCH函数将在该结果数组中找到1，公式将返回该行的价格。
(Video: Excel Lookup With Two Criteria)
First, this video shows how a simple INDEX / MATCH formula works. Then, the formula is changed, to work with multiple criteria. Simple formulas on the worksheet show why the MATCH function can find the correct row.
首先，该视频显示了简单的INDEX / MATCH公式的工作原理。 然后，更改公式，以使用多个条件。 工作表上的简单公式说明了为什么MATCH函数可以找到正确的行。
The timeline is below the video, and you can download the sample file to follow along.
时间线在视频下方，您可以下载示例文件以进行后续操作。
演示地址
0:00 Introduction
0:00简介
0:26 Lookup with One Criterion
0:26一种标准的查找
1:52 Test Each Criterion
1:52测试每个标准
2:22 Test With a Formula
2:22用公式测试
3:26 Multiply the Results
3:26将结果相乘
4:03 INDEX / MATCH Formula
4:03 INDEX / MATCH公式
5:20 Check the Formula
5:20检查公式
5:57 Get the Sample File
5:57获取样本文件
(Get the Excel Lookup With Two Criteria Sample File)
Visit the INDEX/MATCH page on my website to get the Excel Lookup With Two Criteria sample file. This is Example 4 in the sample file.
访问我网站上的INDEX / MATCH页面 ，以获取带有两个条件的Excel查找示例文件。 这是示例文件中的示例4。
翻译自: https://contexturesblog.com/archives/2016/05/26/excel-lookup-with-two-criteria/
