How to convert VLOOPUP error value #NA to 0?
 

Trying to sum a column that was built using VLOOKUP. Several of the values
returned in the column are #NA. The column will not sum. How can we write
the VLOOKUP formula to return error values = to 0 rather than #NA?

How to convert VLOOPUP error value #NA to 0?
 

Embed your vlookup inside an if. Instead of =vlookup(...), use
=if(isna(vlookup(...)),0,vlookup(...))

"Rex" wrote:

Trying to sum a column that was built using VLOOKUP. Several of the values
returned in the column are #NA. The column will not sum. How can we write
the VLOOKUP formula to return error values = to 0 rather than #NA?

How to convert VLOOPUP error value #NA to 0?
 

You could change the Sum() formula:

=SUMIF(A1:A20,"<#N/A")
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Rex" wrote in message
...
Trying to sum a column that was built using VLOOKUP. Several of the values
returned in the column are #NA. The column will not sum. How can we write
the VLOOKUP formula to return error values = to 0 rather than #NA?

How to convert VLOOPUP error value #NA to 0?
 

Another way - adjust your VLOOKUP formulae to the following:

=IF(ISNA(VLOOKUP formula),0,VLOOKUP formula)

and copy this down.

Hope this helps.

Pete

How to convert VLOOPUP error value #NA to 0?
 

General soln is

=IF(ISNA(Your_Formula),0,Your_Formula)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"Rex" wrote in message
...
Trying to sum a column that was built using VLOOKUP. Several of the
values
returned in the column are #NA. The column will not sum. How can we
write
the VLOOKUP formula to return error values = to 0 rather than #NA?