|
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 |
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 |
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 |
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 |
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)) |
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)) |
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. |
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 |
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 |
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 |
If (Vlookup 0) working, but what if Vlookup cell does not exist
Sure,
=IF(ISNA(IF(VLOOKUP(A4,Sat!$A$16:$J$377,9,FALSE)0 ,VLOOKUP(A4,Sat!$A$16:$J$377,9,FALSE),"")),"",IF(V LOOKUP(A4,Sat!$A$16:$J$377,9,FALSE)0,VLOOKUP(A4,S at!$A$16:$J$377,9,FALSE),"")) Squeaky "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 |
If (Vlookup 0) working, but what if Vlookup cell does not exist
=if(iserror(---your formula---),0,---your formula---)
"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 |
If (Vlookup 0) working, but what if Vlookup cell does not exist
=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" 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 |
If (Vlookup 0) working, but what if Vlookup cell does not exist
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" 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 |
If (Vlookup 0) working, but what if Vlookup cell does not exi
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 "Dan" wrote: 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, Sat!$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)) . |
If (Vlookup 0) working, but what if Vlookup cell does not exi
This one is still coming up #N/A.
Thanks, Steve "Sean Timmons" wrote: =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 |
If (Vlookup 0) working, but what if Vlookup cell does not exi
I can't get this one to work.
Thanks, Steve "JBoulton" wrote: =if(iserror(---your formula---),0,---your formula---) "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 |
If (Vlookup 0) working, but what if Vlookup cell does not exi
On Nov 18, 8:58*am, Steve wrote:
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 "Dan" wrote: 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, Sat!$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)) .- Hide quoted text - - 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 |
If (Vlookup 0) working, but what if Vlookup cell does not exi
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 "Dan" wrote: On Nov 18, 8:58 am, Steve wrote: 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 "Dan" wrote: 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(A 4,Sat!$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)) .- Hide quoted text - - 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 . |
| All times are GMT +1. The time now is 01:53 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com