removing #na from a sheet

E

excelquester

how do i remove the # na from showing up in cells where formulas are required
on the sheet ,, how ever there is no information required in the lookup_value
cell.
using the formula =VLOOKUP(A2,Sheet1!A:p,2,FALSE)
Example:

Col a Col b col c
row 2 A1011 name special information
row 3 A1012 name 0
row 4 A1013 name 0

row 6 blank #na #na
row 7 blank #na #na
 
J

Jacob Skaria

Handle that using ISNA() and IF() ..If you are using XL2007 you can try
IFERROR()

=IF(ISNA(VLOOKUP(A2,Sheet1!A:p,2,0)),"",VLOOKUP(A2,Sheet1!A:p,2,0))


If this post helps click Yes
 
J

Jacob Skaria

Remove the = sign from the formulas..or check the other posts

If this post helps click Yes
 

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

how do i remove the 0 in the cells 6
#NA error when doing a look up 1
Creating a Unique List 5
Impossible? Not plotting NA()s 2
Excel How to capture data to a specified excel sheet(first workbook) of two different workbooks from the 0
retrive data from oher sheet on a condition 7
is Retrieving a Cell address from a cell containing a SUMPRODUCTformula a problem ? 5
Shortening a list 6

Top