If 2 conditions exist in a range of cells

[Neophyte]

=IF(AND('SWF08'!$A$2:$A$1905=FallBookings!$C3,'SWF08'!$G$2:$G$1905=FallBookings!$F3),"OK","Review
Finish Date")

Excel 2003
I have tried the above formula but no luck even as an array. I want to check
the date entered in cell F3 against 2 conditions on another sheet, SWF08.
The 2 conditions in sheet FallBookings should be tied to the SWF08 sheet
ranges a2:a1905,g2:g1905 and return one unique "true" else I need to review
the date entered in F3 on FallBookings worksheet. I had thought about
sumproduct() but don't know how to get it to return a text value, so I went
to the if(and()) with "Review Finish Date" results for all the cells even
though I know the value is ok.
I think the formula should be something like, if these 2 cells conditions on
sheet A are met when compared against these 2 ranges of cells on sheet B,
then ok else review entry in cell f3. The data is an item number a2:a1905
and date g2:g1905. In the words of the Highlander, there can be only one.
Thanks to all for looking and any help is greatly appreciated.
Lee

 

[Max]

Think you could try it like this:
=IF(SUMPRODUCT((SWF08!$A$2:$A$1905=FallBookings!$C3)*(SWF08!$G$2:$G$1905=FallBookings!$F3))>0,"OK","Review
Finish Date")
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400, Files:358, Subscribers:55
xdemechanik

 

[Neophyte]

Thanks it worked great. Why the >0? Is it the True/False thing on
sumproduct? Unfortunatly, it returned Review dates so now I have more work
but I can pinpoint the mistakes.
Thanks again,
Lee

 

[Max]

Thanks it worked great.
Welcome, that's good

Why the >0? Is it the True/False thing on sumproduct?

Yes, the product of the 2 conditional arrays produces a resultant array of
1's & 0's, depending on whether the simultaneous criteria is satisfied or
not. Sumproduct then adds it up, hence the >0 check gives you the final
TRUE/FALSE result within the IF.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400, Files:358, Subscribers:55
xdemechanik
---