VLOOKUP and AND function
 

I have multiple named ranges P1POP, P2POP... P12POP. These are on worksheets
P1, P2... P12 respectively.
On another worksheet Accruals I have the following formula
=IF(ISERROR(IF(VLOOKUP(B3,p1pop,1,FALSE),0,1)),1,I F(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

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)

--
Regards
Frank Kabel
Frankfurt, Germany

"Chris" schrieb im Newsbeitrag
...
I have multiple named ranges P1POP, P2POP... P12POP. These are on
worksheets
P1, P2... P12 respectively.
On another worksheet Accruals I have the following formula

=IF(ISERROR(IF(VLOOKUP(B3,p1pop,1,FALSE),0,1)),1,I F(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

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


"Chris" wrote in message
...
I have multiple named ranges P1POP, P2POP... P12POP. These are on
worksheets
P1, P2... P12 respectively.
On another worksheet Accruals I have the following formula
=IF(ISERROR(IF(VLOOKUP(B3,p1pop,1,FALSE),0,1)),1,I F(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