removing #na from a sheet
 

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
--
ty Bob

removing #na from a sheet
 

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
---------------
Jacob Skaria


"excelquester" wrote:

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
--
ty Bob

removing #na from a sheet
 

http://en.allexperts.com/q/Excel-105.../N-Vlookup.htm

"excelquester" wrote:

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
--
ty Bob

removing #na from a sheet
 

One way:

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

--
Biff
Microsoft Excel MVP


"excelquester" wrote in message
...
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
--
ty Bob

removing #na from a sheet
 

Hi,

=if(ISNA(=VLOOKUP(A2,Sheet1!A:P,2,FALSE)),"",=VLOO KUP(A2,Sheet1!A:P,2,FALSE))

"excelquester" wrote:

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
--
ty Bob

removing #na from a sheet
 

getting a error,, something about using = , and - in a formula
--
ty Bob


"Eduardo" wrote:

Hi,

=if(ISNA(=VLOOKUP(A2,Sheet1!A:P,2,FALSE)),"",=VLOO KUP(A2,Sheet1!A:P,2,FALSE))

"excelquester" wrote:

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
--
ty Bob

removing #na from a sheet
 

remove the extraneous = signs


excelquester wrote:

getting a error,, something about using = , and - in a formula

removing #na from a sheet
 

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

If this post helps click Yes
---------------
Jacob Skaria


"excelquester" wrote:

getting a error,, something about using = , and - in a formula
--
ty Bob


"Eduardo" wrote:

Hi,

=if(ISNA(=VLOOKUP(A2,Sheet1!A:P,2,FALSE)),"",=VLOO KUP(A2,Sheet1!A:P,2,FALSE))

"excelquester" wrote:

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
--
ty Bob