VLOOKUP Want to accept "NA" or text as number 0

[lisaspencer]

I have quite a few VLOOKUP tables connected in 1 spreadsheet and it all works fine.

I have one column formula that is basically =columnS - columnN
So the VLOOKUP formula is =IFERROR((VLOOKUP($A1,Database,19,FALSE)-VLOOKUP($A1,Database,14,FALSE))," ")

In columnS (vlookup row 19) are numbers(percentages) and the same in columnN (vlookup row 14) and it works out the difference between the two
But sometimes there is the text "NA" in columnN (vlookup row 14) and I want it to be counted as a 0 but when it is currently text it doesn't count it in the formula.
There are some blanks in columnN (vlookup row 14) too which is why the IFERROR is in front of the formula as I don't want them counted.

Can anyone help with my formula?

 

[Fluff]

Do you mean that if there is text in col N it should subtract 0 from col S?

 

[lisaspencer]

Do you mean that if there is text in col N it should subtract 0 from col S?

Yes. If there is 0 in colN the formula works, but if there is text the formula doesn't work. I have 10,000 lines in the VLOOKUP table so I can't go check every NA in colN. I tried replacing all the NA's in the column with the number 0 but it didn't work for some reason

 

[lisaspencer]

Yes. If there is 0 in colN the formula works, but if there is text the formula doesn't work. I have 10,000 lines in the VLOOKUP table so I can't go check every NA in colN. I tried replacing all the NA's in the column with the number 0 but it didn't work for some reason

Actually ignore me, i've made it work now

 

[Fluff]

Glad you've sorted it & thanks for the feedback.
But one way is

=IFERROR((VLOOKUP($A1,Database,19,FALSE)-N(VLOOKUP($A1,Database,14,FALSE)))," ")