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

[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

 

[Bernard Liengme]

=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

 

[T. Valko]

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)))

 

[Bernard Liengme]

Thanks, Biff. Too little coffee today!
Bernard

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)))

 

[Dan]

Thanks, Biff. Too little coffee today!
Bernard








- Show quoted text -

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))

 

[T. Valko]

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.

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


--
Biff
Microsoft Excel MVP

Thanks, Biff. Too little coffee today!
Bernard








- Show quoted text -

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))

 

[Héctor Miguel]

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 ?

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

hth,
hector.

 

[Jacob Skaria]

=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

 

[JeffK]

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

Try this

 

[Sean Timmons]

=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))

 

[Squeaky]

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

 

[JBoulton]

=if(iserror(---your formula---),0,---your formula---)

 

[Kassie]

=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

 

[Steve]

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

 

[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

 

[Steve]

This one is still coming up #N/A.

Thanks,

Steve

 

[Steve]

I can't get this one to work.

Thanks,

Steve

=if(iserror(---your formula---),0,---your formula---)

 

[Dan]

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 -

=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

 

[Steve]

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