error handling-need to get rid of a value returned as #N/A

G

Gluefoot

I'm trying to grab information from another worksheet but when the line I'm
referencing on the other worksheet isn't available it returns the value #N/A,
which is fine but that's not what I was to display, I just want an empty cell
when it cannot find that info on the other worksheet. Here's an example of
what I'm trying to do....

=VLOOKUP(A2, names, 5, FALSE)

let's say A2 is Bob, if excel cannot find Bob in the "names" array it
returns the value #N/A. I cannot figure out how to make it return a value
that I would rather use. I've been playing with ERROR.TYPE but I can't seem
to get that to work for me because if VLOOKUP actually returns a value I get
another #N/A.
 
M

Michelle

You can use the ISNA function nested in an IF statement...

If(ISNA(VLOOKUP(A2, names, 5, FALSE)),"",VLOOKUP(A2, names, 5, FALSE))

Hope this helps.
 

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

Vlookup returns #N/A when Range is 'false', but returns values when 'true' 4
Vlookup and return sheet name also 2
VLOOKUP source values are a formula result. Getting #N/A 7
ISERROR on VLOOKUP 3
Returning values listed under multiple conditions 6
Look up if a value from an array "A" is in either array "B" or "C" 8
Summing #N/A occurrences 5
using error type and if statement to return a value. 6

Top