0

I have the following sheet:

enter image description here

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

user2051347
  • 1,609
  • 4
  • 23
  • 34

1 Answers1

2

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

Peter Albert
  • 16,917
  • 5
  • 64
  • 88
  • THX for your answer! Doesn`t this only match the `B` colume. However, is it possible to check for both `A` and `B`, because they both are forming a group together! – user2051347 Mar 07 '14 at 10:56
  • OMG! Thx a lot! A problem seems to be that some dates(like `02.06.2003`, you can see it in the picture) are not taken as dates. I tried it multiple times to convert them into dates. Is there any hack for this? – user2051347 Mar 07 '14 at 11:03
  • Ok... do not know why, but with your updated formula it works perfectly well! Any suggestions why? Would be really interesting... – user2051347 Mar 07 '14 at 11:04
  • 1
    regarding the wrong dates: the cells might be formatted as Text. Go to the format dialog and change it to date - and then it'll hopefully work. Regarding the formula: I'll extend the answer in a bit. – Peter Albert Mar 07 '14 at 11:06
  • Thx a lot for your help! I did the `dialog` several times. However nothing really changes.... – user2051347 Mar 07 '14 at 11:11
  • what are your regional settings? are dates entered as DD.MM.YYYY or is this different on your machine (check in Regional settings of windows). If you change the format for the cell/column from text to date - _and then reenter the wrong dates!_, it should work! – Peter Albert Mar 07 '14 at 11:16
  • THX a lot! That can definitely work. However I have over 10969 dates. Is there an automatic solution? – user2051347 Mar 07 '14 at 11:22
  • OK, for future users which possibly face this problem. Import to google spreadsheets and do it there then download it as *.csv, *.xlsx. Works like charm! – user2051347 Mar 07 '14 at 11:38