Deleting N/A value in a spreadsheet with V-Look Up

  • Thread starter Thread starter JOConnor
  • Start date Start date
J

JOConnor

Hi
I have a large spreadsheet of Data that contains a Vlook up to anothe
sheet. I need to sum the figures however the V-Look up is returnin
some N/A values which the autosum doen't recognize. How do I delet
these or make them equal to zero?

thanks
 
Hi
you may change your formulas to
=IF(ISNA(VLOOKUP(...)),"",VLOOKUP(...))

or for summing use
=SUMIF(A1:A100,"<>#NA")
 
One way to do this is to replace each VLOOKUP with a nested IF(), ISERROR(),
and VLOOKUP() function. In the mockup I created I used this formula:
=VLOOKUP(A2,$A$1:$B$4,2)
which returned #N/A.

I replaced it with
=IF(ISERROR(VLOOKUP(A2,$A$1:$B$4,2),0,VLOOKUP(A2,$A$1:$B$4,2))

This formula evaluates the VLOOKUP and returns 0 if the VLOOKUP is #N/A. If
the VLOOKUP does not return an error it performs the VLOOKUP.

Dave O
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

v look up formula to generate a blank line 1
Not showing #N/A 3
trying to sum columns in which some cells read n/a 3
Look up, return value 5
#n/a drving me mad, need to force ignore or remove them - Help! 5
v look up and rounding 2
Cell only updates when I go into formula bar 2
Showing #N/A in a formula 1

Back
Top