vlookup - #N/A

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a worksheet that I am building for home budgeting. I am using the vlookup function to find a certain category in a 2-column array (unsorted) and return the value in the 2nd column. Everything works fine, I have a fair amount of experience with this, however I want to get rid of the #N/A return when there is no match found.

The reason is: if you have several of these functions in a coulmn returning various data and say 2 of them are returning #N/A, then any simple function referencing the column such as sum will return #N/A also.

Here is my function: =VLOOKUP($B14,$BO$10:$BP$201,2,FALSE)

I use the false in Range_Lookup so I do not have to sort the array each time.

Any ideas on how I can get the function to return a zero instead of the #N/A when there is no match found?? Is there some other function I can nest such as IF??

I greatly appreciate any suggestions.

Thanks,
 
I have a worksheet that I am building for home budgeting. I am using the vlookup function to find a certain category in a 2-column array (unsorted) and return the value in the 2nd column. Everything works fine, I have a fair amount of experience with this, however I want to get rid of the #N/A return when there is no match found.

The reason is: if you have several of these functions in a coulmn returning various data and say 2 of them are returning #N/A, then any simple function referencing the column such as sum will return #N/A also.

Here is my function: =VLOOKUP($B14,$BO$10:$BP$201,2,FALSE)

I use the false in Range_Lookup so I do not have to sort the array each time.

Any ideas on how I can get the function to return a zero instead of the #N/A when there is no match found?? Is there some other function I can nest such as IF??

I greatly appreciate any suggestions.

Thanks,
Click to expand...

Very simple:
=IF(ISNA(VLOOKUP($B14,$BO$10:$BP$201,2,FALSE))=TRUE;0;VLOOKUP($B14,$BO$10:$BP$201,2,FALSE))

Function ISNA becomes true if an #N/A!-error occurs.

HTH,

CoRrRan
 
Hi
one way: use
=IF(ISNA(VLOOKUP($B14,$BO$10:$BP$201,2,FALSE)),0,VLOOKUP
($B14,$BO$10:$BP$201,2,FALSE))

-----Original Message-----
I have a worksheet that I am building for home budgeting.
Click to expand...
I am using the vlookup function to find a certain category
in a 2-column array (unsorted) and return the value in the
2nd column. Everything works fine, I have a fair amount of
experience with this, however I want to get rid of the
#N/A return when there is no match found.
The reason is: if you have several of these functions in
Click to expand...
a coulmn returning various data and say 2 of them are
returning #N/A, then any simple function referencing the
column such as sum will return #N/A also.
Here is my function: =VLOOKUP($B14,$BO$10:$BP$201,2,FALSE)

I use the false in Range_Lookup so I do not have to sort the array each time.

Any ideas on how I can get the function to return a zero
Click to expand...
instead of the #N/A when there is no match found?? Is
there some other function I can nest such as IF??
 
You can shorten that a bit by using

=IF(ISNA(MATCH($B14,$BO10:$BO$201,0)),0,VLOOKUP($B14,$BO$10:$BP$201,2,0))

the last 0 = FALSE and since ISNA(MATCH($B14,$BO10:$BO$201,0)) will return
TRUE or FALSE you can
ditch the =TRUE as well

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)


vlookup function to find a certain category in a 2-column array (unsorted)
and return the value in the 2nd column. Everything works fine, I have a fair
amount of experience with this, however I want to get rid of the #N/A return
when there is no match found.returning various data and say 2 of them are returning #N/A, then any simple
function referencing the column such as sum will return #N/A also.#N/A when there is no match found?? Is there some other function I can nest
such as IF??
 
use the if(isna(

=IF(ISNA(VLOOKUP($AW9,sheet1!$A$5:$J$490,6,FALSE))=TRUE,"0",(VLOOKUP($AW9,sheet1!$A$5:$J$490,6,FALSE)))

Good luck!

Gre
 
you could use SUMIF instead of SUM, for example:
=sumif($BO$10:$BP$201,"<>#N/A",$BO$10:$BP$201) will "sum" only the values
that weren't returned as #N/A. It doesn't get rid of the #N/A, but ignores
them. Not quite what you asked, but you asked for the very same reason that
I searched and found this remedy.
 
Try this adjusted to your range:

=IF(ISNA(VLOOKUP(A1,G1:H3,2,0)),0,(VLOOKUP(A1,G1:H3,2,0)))

HTH
Regards,
Howard
 
Back
Top