Remember Me?

#1
November 16th 09, 06:28 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 1,814
If (Vlookup 0) working, but what if Vlookup cell does not exist

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

#2
November 16th 09, 06:33 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 4,393
If (Vlookup 0) working, but what if Vlookup cell does not exist

=IF(OR(ISNA(VLOOKUP(A4,Sat!\$A\$16:\$J\$377,9,FALSE)),
VLOOKUP(A4,Sat!\$A\$16:\$J\$377,9,FALSE=0,"",VLOOKUP(A 4,Sat!\$A\$16:\$J\$377,9,FALSE))
best wishes
--
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP

"Steve" wrote in message
...
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

#3
November 16th 09, 06:51 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Nov 2006 Posts: 15,768
If (Vlookup 0) working, but what if Vlookup cell does not exist

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(V LOOKUP(A4,Sat!\$A\$16:\$J\$377,9,0)=0,"",VLOOKUP(A4,Sa t!\$A\$16:\$J\$377,9,0)))

--
Biff
Microsoft Excel MVP

"Bernard Liengme" wrote in message
...
=IF(OR(ISNA(VLOOKUP(A4,Sat!\$A\$16:\$J\$377,9,FALSE)),
VLOOKUP(A4,Sat!\$A\$16:\$J\$377,9,FALSE=0,"",VLOOKUP(A 4,Sat!\$A\$16:\$J\$377,9,FALSE))
best wishes
--
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP

"Steve" wrote in message
...
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

#4
November 16th 09, 07:08 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 4,393
If (Vlookup 0) working, but what if Vlookup cell does not exist

Thanks, Biff. Too little coffee today!
Bernard

"T. Valko" wrote in message
...
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(V LOOKUP(A4,Sat!\$A\$16:\$J\$377,9,0)=0,"",VLOOKUP(A4,Sa t!\$A\$16:\$J\$377,9,0)))

--
Biff
Microsoft Excel MVP

"Bernard Liengme" wrote in message
...
=IF(OR(ISNA(VLOOKUP(A4,Sat!\$A\$16:\$J\$377,9,FALSE)),
VLOOKUP(A4,Sat!\$A\$16:\$J\$377,9,FALSE=0,"",VLOOKUP(A 4,Sat!\$A\$16:\$J\$377,9,FALSE))
best wishes
--
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP

"Steve" wrote in message
...
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

#5
November 16th 09, 11:46 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Nov 2009 Posts: 2
If (Vlookup 0) working, but what if Vlookup cell does not exist

On Nov 17, 5:08*am, "Bernard Liengme"
wrote:
Thanks, Biff. Too little coffee today!
Bernard

"T. Valko" wrote in message

...

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(V LOOKUP(A4,Sat!\$A\$16:\$J\$37*7,9,0)=0,"",VLOOKUP(A4,S at!\$A\$16:\$J\$377,9,0)))

--
Biff
Microsoft Excel MVP

"Bernard Liengme" wrote in message
...
=IF(OR(ISNA(VLOOKUP(A4,Sat!\$A\$16:\$J\$377,9,FALSE)),
VLOOKUP(A4,Sat!\$A\$16:\$J\$377,9,FALSE=0,"",VLOOKUP(A 4,Sat!\$A\$16:\$J\$377,9,FALS*E))
best wishes
--
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP

"Steve" wrote in message
...
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,FA*LSE),"")

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

Thanks,

Steve- Hide quoted text -

- 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,F A*LSE))

#6
November 17th 09, 01:58 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Nov 2006 Posts: 15,768
If (Vlookup 0) working, but what if Vlookup cell does not exist

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

"Dan" wrote in message
...
On Nov 17, 5:08 am, "Bernard Liengme"
wrote:
Thanks, Biff. Too little coffee today!
Bernard

"T. Valko" wrote in message

...

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(V LOOKUP(A4,Sat!\$A\$16:\$J\$37*7,9,0)=0,"",VLOOKUP(A4,S at!\$A\$16:\$J\$377,9,0)))

--
Biff
Microsoft Excel MVP

"Bernard Liengme" wrote in message
...
=IF(OR(ISNA(VLOOKUP(A4,Sat!\$A\$16:\$J\$377,9,FALSE)),
VLOOKUP(A4,Sat!\$A\$16:\$J\$377,9,FALSE=0,"",VLOOKUP(A 4,Sat!\$A\$16:\$J\$377,9,FALS*E))
best wishes
--
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP

"Steve" wrote in message
...
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,FA*LSE),"")

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

Thanks,

Steve- Hide quoted text -

- 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,F A*LSE))

#7
November 17th 09, 03:44 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Sep 2006 Posts: 434
If (Vlookup 0) working, but what if Vlookup cell does not exist

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.

#8
November 17th 09, 06:20 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Mar 2009 Posts: 8,520
If (Vlookup 0) working, but what if Vlookup cell does not exist

=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
---------------
Jacob Skaria

"Steve" wrote:

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

#9
November 17th 09, 06:21 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jan 2009 Posts: 53
If (Vlookup 0) working, but what if Vlookup cell does not exist

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

Try this

"Steve" wrote:

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

#10
November 17th 09, 06:23 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Aug 2006 Posts: 1,696
If (Vlookup 0) working, but what if Vlookup cell does not exist

=IF(OR(iISNA(VLOOKUP(A4,Sat!\$A\$16:\$J\$377,9,FALSE)) ,VLOOKUP(A4,Sat!\$A\$16:\$J\$377,9,FALSE)=0),"",VLOOKU P(A4,Sat!\$A\$16:\$J\$377,9,FALSE))

"Steve" wrote:

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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Gary Excel Worksheet Functions 7 March 2nd 07 12:34 AM ericgo Excel Worksheet Functions 4 October 20th 06 07:46 PM SMRE Excel Worksheet Functions 1 September 29th 06 04:50 PM njuneardave Excel Discussion (Misc queries) 7 June 27th 06 07:43 PM jdeshpa Excel Worksheet Functions 2 November 22nd 05 10:12 PM

All times are GMT +1. The time now is 05:36 AM.