If (Vlookup >0) working, but what if Vlookup cell does not exist

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

This formula works if the data is present, however, I'm getting #N/A if the
lookup cell (A4) doesn't exist, which it may not on some days.

=IF(VLOOKUP(A4,Sat!$A$16:$J$377,9,FALSE)>0,VLOOKUP(A4,Sat!$A$16:$J$377,9,FALSE),"")

Can this formula be modified to also produce "" if the data representing A4
does not exist ?

Thanks,

Steve
 
=IF(OR(ISNA(VLOOKUP(A4,Sat!$A$16:$J$377,9,FALSE)),
VLOOKUP(A4,Sat!$A$16:$J$377,9,FALSE=0,"",VLOOKUP(A4,Sat!$A$16:$J$377,9,FALSE))
best wishes
 
You have to separate the OR conditions.

If ISNA is TRUE then the separate VLOOKUP will return #N/A causing the OR to
fail.

=IF(ISNA(VLOOKUP(A4,Sat!$A$16:$J$377,9,0)),"",IF(VLOOKUP(A4,Sat!$A$16:$J$377,9,0)=0,"",VLOOKUP(A4,Sat!$A$16:$J$377,9,0)))
 
Thanks, Biff. Too little coffee today!
Bernard

T. Valko said:
You have to separate the OR conditions.

If ISNA is TRUE then the separate VLOOKUP will return #N/A causing the OR
to fail.

=IF(ISNA(VLOOKUP(A4,Sat!$A$16:$J$377,9,0)),"",IF(VLOOKUP(A4,Sat!$A$16:$J$377,9,0)=0,"",VLOOKUP(A4,Sat!$A$16:$J$377,9,0)))
Click to expand...
 
Thanks, Biff. Too little coffee today!
Bernard








- Show quoted text -
Click to expand...

Would you not be better to test if A4 is blank rather than testing the
result of the VLookup formula? Using ISNA will filter genuine NA
messages too.

=IF(ISBLANK(A4),"",VLOOKUP(A4,Sat!$A$16:$J$377,9,FA­LSE))
 
Would you not be better to test if A4 is blank
rather than testing the result of the VLookup
formula? Using ISNA will filter genuine NA
messages too.
Click to expand...

It sounded like that's what the OP wanted to.


--
Biff
Microsoft Excel MVP


Thanks, Biff. Too little coffee today!
Bernard








- Show quoted text -
Click to expand...

Would you not be better to test if A4 is blank rather than testing the
result of the VLookup formula? Using ISNA will filter genuine NA
messages too.

=IF(ISBLANK(A4),"",VLOOKUP(A4,Sat!$A$16:$J$377,9,FA­LSE))
 
hi, Steve !
This formula works if the data is present, however, I'm getting #N/A if the lookup cell (A4) doesn't exist, which it may not on some days.

=IF(VLOOKUP(A4,Sat!$A$16:$J$377,9,FALSE)>0,VLOOKUP(A4,Sat!$A$16:$J$377,9,FALSE),"")

Can this formula be modified to also produce "" if the data representing A4 does not exist ?
Click to expand...

i.e. =if(countif(sat!$a$16:$a$377,a4),vlookup(a4,sat!$a$16:$j$377,9,0),"")

hth,
hector.
 
=IF(ISNA(VLOOKUP(A4,SAT!$A$16:$J$377,9,0)),"",
IF(VLOOKUP(A4,SAT!$A$16:$J$377,9,0)>0,
VLOOKUP(A4,SAT!$A$16:$J$377,9,0),""))

If this post helps click Yes
 
=if(a4="","",IF(VLOOKUP(A4,Sat!$A$16:$J$377,9,FALSE)>0,VLOOKUP(A4,Sat!$A$16:$J$377,9,FALSE),""))

Try this
 
=IF(OR(iISNA(VLOOKUP(A4,Sat!$A$16:$J$377,9,FALSE)),VLOOKUP(A4,Sat!$A$16:$J$377,9,FALSE)=0),"",VLOOKUP(A4,Sat!$A$16:$J$377,9,FALSE))
 
Sure,

=IF(ISNA(IF(VLOOKUP(A4,Sat!$A$16:$J$377,9,FALSE)>0,VLOOKUP(A4,Sat!$A$16:$J$377,9,FALSE),"")),"",IF(VLOOKUP(A4,Sat!$A$16:$J$377,9,FALSE)>0,VLOOKUP(A4,Sat!$A$16:$J$377,9,FALSE),""))

Squeaky
 
=IF(ISNA(VLOOKUP(A4,Sat!$A$16:$J$377,9,FALSE)),"",VLOOKUP(A4,Sat!$A$16:$J$377,9,FALSE))
--
HTH

Kassie

Replace xxx with hotmail
 
Thanks everybody.

I'm amazed at the variety of the responses. Most worked great. Those that
didn't didn't because I wasn't too clear on what I needed. I'll just ID those
that worked with the green checkmark, and comment on the others. Thanks again.

Steve
 
I guess I wasn't too clear, because the A4 on this sheet will always be
there. It's the A4 lookup on Sat that may or may not be there.

Thaanks,

Steve
 
I guess I wasn't too clear, because the A4 on this sheet will always be
there. It's the A4 lookup on Sat that may or may not be there.

Thaanks,

Steve







- Show quoted text -
Click to expand...

=IF(OR(ISBLANK(A4),ISBLANK(Sat!A4)),"",VLOOKUP(A4,Sat!$A$16:$J
$377,9,FA­LSE))

This will test both A4 on the current sheet and A4 on the sheet Sat
 
It's still producing the #N/A when the data in the A4 cell not on the Sat
tab. Also, the when it is on the Sat tab, it won't be in Sat!A4, in fact, it
may be anywhere in the A column.

Thanks,

Steve
 
Back
Top