Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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   Report Post  
Old 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
Default 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



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


Similar Threads
Thread Thread Starter Forum Replies Last Post
VLOOKUP not working??? Gary Excel Worksheet Functions 7 March 2nd 07 12:34 AM
Cell reference in vlookup not working ericgo Excel Worksheet Functions 4 October 20th 06 07:46 PM
Vlookup not working SMRE Excel Worksheet Functions 1 September 29th 06 04:50 PM
VLOOKUP - returning the searched for value if it does not exist njuneardave Excel Discussion (Misc queries) 7 June 27th 06 07:43 PM
Getting #N/A from Vlookup when matching value exist in the lookup data range. jdeshpa Excel Worksheet Functions 2 November 22nd 05 10:12 PM


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

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017