excel formula to check through all the entries in a month for a specific entry

[Alwinaz]

Hi there

I have the following sheet


64 01-Feb-11 01-Mar-11 448 ADJ 58 YES

65 01-Mar-11 01-Apr-11 448 ADJ 58 NO

66 01-Mar-11 01-Apr-11 -896 COR 58 NO

67 01-Mar-11 01-Apr-11 56 Monthly 58 NO


I need a formula that will check whether there was an entry named "COR" in the next month. I have used the following formula =IF(AND(E64="ADJ",VLOOKUP(VLOOKUP(C64,B62:B68,1,FALSE),B62:E68,4,FALSE)="ADJ"),"YES","NO") in column G to first locate the month following the entry in column B and then it must test whether the enty in column E = "ADJ". this Part is working, however i also need check whether an enrty "COR" also occured in the date found with this formula =VLOOKUP(C64,B62:B68,1,FALSE). the vlookup however only stops at the first entry (row 65) of the month. is there another formula that i can use that will check all the entries for that specific month.

 

[Luke M]

I think this is what you're looking for...


Formula in G64

=SUMPRODUCT(--(B65:B$80=C64),--(E65:E$80="COR"))>0


Formula looks at all the cells in the following rows to see if any have the same date in column B that is currently in C64, AND if it does then it also checks if the col E has the word COR in it. If so, return True, else False.