if isna and vlookup together

G

Guest

Hi
I have a vlookup formula where some answers are #n/a to tidy up i have used
if isna the formula is quite long is there a better way than formula below
i.e do I have to write vlookup again?
=if(isna(vlookup(a1,sheet2!a:b,2,false),0,vlookup(a1,sheet2!a:b,2,false))
Thanks
Tina
 
B

Bob Phillips

Tina,

Another way is something similar, that is test the lookup values for error

=IF(ISNUMBER(MATCH(A1,Sheet2!,A;A,)),...

not really much different.
 
J

Jason Morin

=if(countif(sheet2!a:a,a1),vlookup(a1,sheet2!,a:b,2,0),0)

HTH
Jason
Atlanta, GA
 

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 returning Errors (ISNA formula) 3
IF, ISNA & VLOOKUP 5
ISNA Formula returning "False" instead of value 4
EXCEL - IF(IFERROR(VLOOKUP question 0
Vlookup on more than one sheet 5
=IF(NOT(ISNA(VLOOKUP Function 2
Vlookup #n/a 6
isna vlookup #n/a but not false 1

Top