| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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)) . |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLOOKUP not working??? | Excel Worksheet Functions | |||
| Cell reference in vlookup not working | Excel Worksheet Functions | |||
| Vlookup not working | Excel Worksheet Functions | |||
| VLOOKUP - returning the searched for value if it does not exist | Excel Discussion (Misc queries) | |||
| Getting #N/A from Vlookup when matching value exist in the lookup data range. | Excel Worksheet Functions | |||
Hybrid Mode
