#N/A

[andremayer]

Hello,

I have a calculation, it works when it finds what I want it to look up which is good, but when it doesn't find what I am looking for, this #N/A shows up. It's messing up with other formulas because of that. So I am wondering how I can fix this so that if it finds what I'm looking for it Puts a 1, if it doesn't it should put a 0.

Here's my formula
=VLOOKUP(K18,DATA!A1:A11470,1,0)

So, I want it to look at a number on one of my sheets, if it finds that number in my DATA sheet, I want it to bring back that number, if it's not in the array (A1:A11470) I want it to say 0

Am I doing this right?

 

[mchel_27]

Try:

=IF(ISNA(VLOOKUP(K18,DATA!A1:A11470,1,FALSE)),0,(VLOOKUP(K18,DATA!A1:A11470,1,FALSE)))

 

[barry houdini]

You could try COUNTIF, e.g.

=IF(COUNTIF(DATA!A1:A11470,K18),1,0)

 

[UniMord]

Or you could use: =IFERROR(VLOOKUP(K18,DATA!A1:A11470,1,0), 0)

 

[barry houdini]

Sorry, I misinterpreted what you wanted, with COUNTIF you'd need to use this version

=IF(COUNTIF(DATA!A1:A11470,K18),K18,0)