使用= VLOOKUP检查两个值 [英] Check two values with =VLOOKUP
问题描述
我有以下表格:
如您所见,有两组数据。一组在 A
和 B
中,应该针对第一组组进行检查的组在 D
和 E
As you can see, there are two groups of data. One group is in A
and B
and the group which should be checked against the first group group is in D
and E
现在我想检查第二组反对第一组中的所有值。
Now I want to check each value in the second group against all the values in the first group.
我试图使用 = VLOOKUP(D2:E2; $ A $ 2:$ B $ 10 ; 2; FALSE)
,但只给我 #Value
而不是布尔型。
I tried to use =VLOOKUP(D2:E2;$A$2:$B$10;2;FALSE)
but that only gives me #Value
and no boolean back.
我非常感谢你对这个问题的回答!
I really appreciate your answer on this problem!!!
推荐答案
您可以在F2中使用以下公式:
You can use the following formula in F2:
=NOT(ISERROR(MATCH(D2&E2,$A$2:$A$10&$B$2:$B$10,0)))
输入 Ctrl - Shift - 输入的公式。当您以这种方式输入时,Excel知道它是一个数组公式。这意味着它将执行内部操作(&
)并返回每个参数的数组 - 即 $ A $ 2:$ A $ 10 & $ B $ 2:$ B $ 10
将成为('23.10.2002a','07 .11.2002a',...)
* - 然后将匹配 D2& E2
,即 01.07.2002f
。请注意,此过程相当计算强度,因为阵列将重新计算为F列中的每个单元格。
Enter the formula with Ctrl-Shift-Enter. When you enter it this way, Excel knows that it is an array formula. This means that it'll perform the inner operations (the &
) and return an array for each parameter - i.e. $A$2:$A$10&$B$2:$B$10
will become ('23.10.2002a', '07.11.2002a', ...)
* - which will then be matched against D2&E2
, i.e. 01.07.2002f
. Note that this process is quite calculation intense, as the array gets re-calculated for every cell in column F.
或者,您可以简单地使用此公式(输入为正常公式):
Alternatively, you can simple use this formula (entered as a normal formula):
=COUNTIFS($A:$A,D2,$B:$B,E2)
这应该更高效 - 但只能在Excel 2007 +中工作。
This should be much more efficient - but only works in Excel 2007+.
* Excel将将日期07.11.2002转换为一个数字,即A2和B2将导致 37567a
而不是$ code> 07.11。 2002a
*Excel will convert the date 07.11.2002 to a number, i.e. A2&B2 will result in 37567a
, not 07.11.2002a
这篇关于使用= VLOOKUP检查两个值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!