Check two values with =VLOOKUP

问题:

I have the following sheet:

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.

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!!!

回答1:

You can use the following formula in F2:

=NOT(ISERROR(MATCH(D2&E2,$A$2:$A$10&$B$2:$B$10,0)))

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)

This should be much more efficient - but only works in Excel 2007+.

*Excel will convert the date 07.11.2002 to a number, i.e. A2&B2 will result in 37567a, not 07.11.2002a