Excel表查找匹配两列的值 [英] Excel table lookup matching values of two columns

问题描述

我想创建一个与两列匹配的表查找公式。例如，假设我想在 Type 列的行中找到 Letter 列的值双年度，并且结果列为警告。 >

I'd like to create a table lookup formula that matches two columns. For instance, suppose I'd like to find the value of the Letter column at the row where the Type column is Biennial and the Result column is Warning.

  A               B            C
1 Letter          Type         Result
2 A               Annual       Exceeds
3 B               Biennial     Warning
4 C               Biennial     DevelopmentNeeded
5 D               Biennial     PartiallyMeets
6 E               Annual       Meets

 
公式看起来像是要完成这个？
 解决方案 
推荐答案
 SUMPRODUCT多重标准。这是最方便的，当想要查找数字值，但它可以调整以查找字符串值以及。作为一个奖励，你可以避免使用数组公式。
This particular problem can be tackled with the following formula (indentation added for legibility, which you can do in Excel formulas using ALT + ENTER):
这个特殊的问题可以用下面的公式来处理（为了易读性添加缩进，你可以在Excel使用ALT + ENTER的公式）：
=INDEX(
       $A$2:$A$6,
       SUMPRODUCT(
                  ($B$2:$B$6 = "Biennial") *
                  ($C$2:$C$6 = "Warning") *
                  ROW($A$2:$A$6)
                 ) - 1
       )

First, SUMPRODUCT() is used to filter out the proper rows using ($B$2:$B$6 = "Biennial") and ($C$2:$C$6 = "Warning"); the multiplication operator * functions as an AND operator (the + operator would function as an OR operator).
首先，使用SUMPRODUCT使用（$ B $ 2：$ B $ 6 =双年度）和（$ C $ 2：$ C $ 6 =警告）;乘法运算符 * 用作 AND 运算符（ + 运算符将作为 OR 运算符）。
Then the result is multiplied by ROW($A$2:$A$6) to find the particular row that has the combination. SUMPRODUCT() then adds everything up, which in this case gives us 3. As the result sought is actually on row 2 due to the column  headings, we subtract 1. By applying the INDEX() function, we get the desired result: B.
然后结果乘以 ROW（$ A $ 2：$ A $ 6）找到具有组合的特定行。 SUMPRODUCT（）然后添加一切，在这种情况下给我们 3 。因为所寻求的结果实际上是在第2行，由于列标题，我们减1.通过应用INDEX（）函数，我们得到所需的结果： B  p> 
 
 
谨防这种情况当且仅当所寻找的组合是唯一的。如果寻求的组合存在多于一次，则这将分解。
Beware though that this is the case if and only if the combination sought is unique. If the combination sought exists more than once, this will break down.

                        
这篇关于Excel表查找匹配两列的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！