VLOOKUP and AND function

[Guest]

I have multiple named ranges P1POP, P2POP... P12POP. These are on worksheets
P1, P2... P12 respectively.
On another worksheet Accruals I have the following formul
=IF(ISERROR(IF(VLOOKUP(B3,p1pop,1,FALSE),0,1)),1,IF(VLOOKUP(B3,p1pop,1,FALSE),0,1))

This returns a 0 (zero) if B3 is found in the range P1POP or 1 (one) if not.
I want to use this to return a 0(zero) if B3 is found in any of the ranges as
above or 1 (one) if not found. It works okay for just the one range but I
cannot get it to look at all the ranges. Also VLOOKUP does not work with a 3D
range

Help

Chris

 

[Frank Kabel]

Hi
not tested but try
=IF(SUMPRODUCT(COUNTIF(INDIRECT("p" & ROW(1:12) & "pop")))>0,0,1)

or shorter:
=--(SUMPRODUCT(COUNTIF(INDIRECT("p" & ROW(1:12) & "pop")))=0)

 

[JulieD]

Hi Chris

not sure if this will work, but it might be worth you trying you can use OR
to test to see if at least one from multiple conditions are true
=IF(OR(test1,test2,test3....test12),0,1)

Hope this helps
Cheers
JulieD