vlookup - #N/A
 

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.

"chappy88" wrote:

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,
--
Jeff

vlookup - #N/A
 

Try this adjusted to your range:

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

HTH
Regards,
Howard

"Cheryl" wrote in message
...
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.

"chappy88" wrote:

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,
--
Jeff