how to remove #n/a error in excel vlookup b/c value is not found?

  • Thread starter Thread starter B. Franklin Saunders
  • Start date Start date
B

B. Franklin Saunders

I'm using vlookup in excel to pull select data from a worksheet. The problem
is that the value I'm seeking isn't always in the table array. So, I get a
#n/a error. The #n/a then prevents me from summing the data that I'm
collecting using the vlookup. How do I make the #n/a go away or equal zero
so I can sum the data?
 
Try one of these:

Returns a blank:

=IF(ISNA(VLOOKUP(.....)),"",VLOOKUP(.....))

Returns a 0:

=IF(ISNA(VLOOKUP(.....)),0,VLOOKUP(.....))
 
It worked. Thank you!!

T. Valko said:
Try one of these:

Returns a blank:

=IF(ISNA(VLOOKUP(.....)),"",VLOOKUP(.....))

Returns a 0:

=IF(ISNA(VLOOKUP(.....)),0,VLOOKUP(.....))
Click to expand...
 
Is this still true for Excel 2007? Because I tried the top one, and it still
left 0's in the column.
 
Try using if(iserror(vlookup(Value,Table,column,0)),"your
response",(vlookup(Value,Table,column,0)))

Hope that helps
 
When I add two VLOOKUP formulars with the adjust with the below "IF (ISNA)",
and one items of the two is found, Excel is not displaying the item found,
but a Zero. What should I do?
 
If you're using Excel 2007 try one of these:

=IFERROR(VLOOKUP(.....),"")
=IFERROR(VLOOKUP(.....),0)

Note those will trap *all* errors, not just #N/A.
 
I am relatively new to entering formulas in an excel spreadsheet. I am
trying to remove the '#N/A' error & can't get any of the solutions suggested
to work. Here is my formula:
=((+K12-I12+1)*N12/LOOKUP(P12,S101:S105,T101:T105))
I cannot get the "IF(ISNA' to work. What am I missing?

Thank you.
 
Try
=IF(ISNA(LOOKUP(P12,S101:S105,T101:T105)),"",
(+K12-I12+1)*N12/LOOKUP(P12,S101:S105,T101:T105))

best wishes
 
I also wantd to thank T. Valko for the post of the ISNA answer it also helped
me in solving the Vlookup issue of #N/A.
 
Thank you- this was great!!

T. Valko said:
Try one of these:

Returns a blank:

=IF(ISNA(VLOOKUP(.....)),"",VLOOKUP(.....))

Returns a 0:

=IF(ISNA(VLOOKUP(.....)),0,VLOOKUP(.....))
Click to expand...
 
Back
Top