|
get rid of #N/A in a VLOOKUP
I need to replace the #N/A values to be blank within a VLOOKUP formula. How
do I do this? I've tried the following but excel doesn't seem to like it: =IF(ISBLANK(VLOOKUP($A6,'Sept 9'!$A$1:$U$47,14,FALSE),"",(VLOOKUP($A6,'Sept 9'!$A$1:$U$47,14,FALSE)) |
Hi,
You are missing a closing parens in the formula and I usually use ISERROR. =IF(ISERROR(VLOOKUP($A6,'Sept 9'!$A$1:$U$47,14,FALSE)),"",VLOOKUP($A6,'Sept 9'!$A$1:$U$47,14,FALSE)) HTH -- Ken Hudson "AZExcelNewbie" wrote: I need to replace the #N/A values to be blank within a VLOOKUP formula. How do I do this? I've tried the following but excel doesn't seem to like it: =IF(ISBLANK(VLOOKUP($A6,'Sept 9'!$A$1:$U$47,14,FALSE),"",(VLOOKUP($A6,'Sept 9'!$A$1:$U$47,14,FALSE)) |
Replace ISBLANK with ISERROR. Also, it looks like you're missing the ending
) for your ISBLANK function (posting error perhaps). "AZExcelNewbie" wrote: I need to replace the #N/A values to be blank within a VLOOKUP formula. How do I do this? I've tried the following but excel doesn't seem to like it: =IF(ISBLANK(VLOOKUP($A6,'Sept 9'!$A$1:$U$47,14,FALSE),"",(VLOOKUP($A6,'Sept 9'!$A$1:$U$47,14,FALSE)) |
Hello,
The proper formula would be ... =IF(ISNA(VLOOKUP($A6,'Sept 9'!$A$1:$U$47,14,0)),"",VLOOKUP($A6,'Sept 9'!$A$1:$U$47,14,0)) Although I would suggest downloading Laurent Longre's Morefunc.xll add-in. Found he http://xcell05.free.fr/english/index.html (english). You could then shorten it to ... =IF(SETV(VLOOKUP($A6,'Sept 9'!$A$1:$U$47,14,0)),"",GETV()) Makes it a little simpler to read/understand/troubleshoot. HTH -- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) "AZExcelNewbie" wrote in message ... I need to replace the #N/A values to be blank within a VLOOKUP formula. How do I do this? I've tried the following but excel doesn't seem to like it: =IF(ISBLANK(VLOOKUP($A6,'Sept 9'!$A$1:$U$47,14,FALSE),"",(VLOOKUP($A6,'Sept 9'!$A$1:$U$47,14,FALSE)) |
Why ISERROR? Why not ISNA?
-- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) "Ken Hudson" wrote in message ... Hi, You are missing a closing parens in the formula and I usually use ISERROR. =IF(ISERROR(VLOOKUP($A6,'Sept 9'!$A$1:$U$47,14,FALSE)),"",VLOOKUP($A6,'Sept 9'!$A$1:$U$47,14,FALSE)) HTH -- Ken Hudson "AZExcelNewbie" wrote: I need to replace the #N/A values to be blank within a VLOOKUP formula. How do I do this? I've tried the following but excel doesn't seem to like it: =IF(ISBLANK(VLOOKUP($A6,'Sept 9'!$A$1:$U$47,14,FALSE),"",(VLOOKUP($A6,'Sept 9'!$A$1:$U$47,14,FALSE)) |
Hi Zack,
Just my preference. ISNA accounts for the #NA error which is in the original posting. I added the ISERROR suggestion to alert the poster that it would cover the gamut of possible errors and is what I usually want to test. -- Ken Hudson "Zack Barresse" wrote: Why ISERROR? Why not ISNA? -- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) "Ken Hudson" wrote in message ... Hi, You are missing a closing parens in the formula and I usually use ISERROR. =IF(ISERROR(VLOOKUP($A6,'Sept 9'!$A$1:$U$47,14,FALSE)),"",VLOOKUP($A6,'Sept 9'!$A$1:$U$47,14,FALSE)) HTH -- Ken Hudson "AZExcelNewbie" wrote: I need to replace the #N/A values to be blank within a VLOOKUP formula. How do I do this? I've tried the following but excel doesn't seem to like it: =IF(ISBLANK(VLOOKUP($A6,'Sept 9'!$A$1:$U$47,14,FALSE),"",(VLOOKUP($A6,'Sept 9'!$A$1:$U$47,14,FALSE)) |
True. I just am of the thinking, "If the glove fits..." Ya know? I mean,
I can do almost anything with VBA, but that doesn't mean use it when a simple worksheet function would do the trick, does it? I know we're talking petty change here, I've just seen these semantics often times get blown up on a proportionately bigger scale with the same priciples. Take care. -- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) "Ken Hudson" wrote in message ... Hi Zack, Just my preference. ISNA accounts for the #NA error which is in the original posting. I added the ISERROR suggestion to alert the poster that it would cover the gamut of possible errors and is what I usually want to test. -- Ken Hudson "Zack Barresse" wrote: Why ISERROR? Why not ISNA? -- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) "Ken Hudson" wrote in message ... Hi, You are missing a closing parens in the formula and I usually use ISERROR. =IF(ISERROR(VLOOKUP($A6,'Sept 9'!$A$1:$U$47,14,FALSE)),"",VLOOKUP($A6,'Sept 9'!$A$1:$U$47,14,FALSE)) HTH -- Ken Hudson "AZExcelNewbie" wrote: I need to replace the #N/A values to be blank within a VLOOKUP formula. How do I do this? I've tried the following but excel doesn't seem to like it: =IF(ISBLANK(VLOOKUP($A6,'Sept 9'!$A$1:$U$47,14,FALSE),"",(VLOOKUP($A6,'Sept 9'!$A$1:$U$47,14,FALSE)) |
| All times are GMT +1. The time now is 04:42 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com