Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Chris
 
Posts: n/a
Default 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
  #2   Report Post  
Frank Kabel
 
Posts: n/a
Default

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


  #3   Report Post  
JulieD
 
Posts: n/a
Default

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 02:18 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"