Field = #N/A - Fine but how do I lookup #N/A in 'IF' Statement 1

[KLewisBPM]

I have a result of a vlookup which is #N/A I expected this and now want to create an If statement that uses #N/A.

Row Column A Column B

1 8 =if(A1=#N/A,0,1) Result = 1
2 #N/A =if(A1=#N/A,0,1) Result = #N/A
3 16 =if(A1=#N/A,0,1) Result = 1
4 6 =if(A1=#N/A,0,1) Result = 1
5 #N/A =if(A1=#N/A,0,1) Result = #N/A

B2 and B5 should = 0



Kind Regards

Kelley Lewis

 

[gizmo1973]

Can you not replace all #n/a to somthing else then IF statement on that.
Find and replace will work on #n/a

Regards, Phil.

M.U.F.C. Show your true support here:

http://www.shareholdersunited.org/join.php

"Shares not Shirts

 

[ManxDom]

try using the ISNA function



Dom

 

[KLewisBPM]

Phil,

Trouble is this excel sheet will be looking up to a database and most of the sheets will be hidden. everytime the database refreshes someone will need to do the find and replace.

If all else fails I could do a macro that performs a find and replace upon refresh.

I just assumed it would be a bit easier to allow it in a formula!

Cheers

Kind Regards

Kelley Lewis

 

[gizmo1973]

If you are doing a lookup to get the data then the isna function will replace the #n/a with data of your choice from source.
If you are not sure how to use isna post back

Regards, Phil.

M.U.F.C. Show your true support here:

http://www.shareholdersunited.org/join.php

"Shares not Shirts

 

[KLewisBPM]

Dom,

Thanks, add an extra column to determine ISNA = True/False.

Perfect Thanks!!

Kind Regards

Kelley Lewis

 

[kphu]

I have to give a star to ManxDOM, this has been a problem that I had run into the past and didn't spend the time to look for a function that finds NA.

You learn something new everyday.

Ken

 

[KenWright]

Noooooo. Fix the problem at source, ie within your VLOOKUP:-

=IF(ISNA(VLOOKUP(....)),"",VLOOKUP(....))

or

=IF(ISNA(VLOOKUP(....)),0,VLOOKUP(....))

etc

There is no point in fixing or working round a multitude of symptoms when the problem can be fixed at the root of the issue.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------

 

[gizmo1973]

Ken is right, that's what my post is trying to say but you seemed happy with the other offered solution!

Regards, Phil.

M.U.F.C. Show your true support here:

http://www.shareholdersunited.org/join.php

"Shares not Shirts

 

[kphu]

manxdom provided the actual function that is the solution.

advance users will know how to use the function within an if statement but can't use if their not aware that the function exist.

I've tried find and replace "#N/A" but it didn't find it.

sorry phil provide the detailed solution and the star you shall receive.

 

[gizmo1973]

kphu,

I'm not refering to the find and replace I'm refering to the post whereby I stated use the ISNA function (albeit Dom beat me too it) and asked that anyone post back if they were unclear as to the use.
I'm not trawling for stars only trying to provide the best solution.

Regards, Phil.

M.U.F.C. Show your true support here:

http://www.shareholdersunited.org/join.php

"Shares not Shirts

 

[KLewisBPM]

There are a couple of functions i have found now that perform the same thing.

=ISERROR(A1)

Etc. Thanks all.

ManxDom - Star awarded!

Kind Regards

Kelley Lewis

 

[xlbo]

This is not about star grabbing - it is about providing the right info.
Gizmo and Ken are right - you should sort the issue at the source.

ISERROR is NOT the same as ISNA

ISNA traps ONLY the #N/A error
ISERROR traps ALL errors

vlookups can also give the #REF! error which you would NOT want to trap in this way

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[KenWright]

Apologies Phil - Hadn't picked up on your post there, but you were quite correct.

KLewis - If your roof leaked would you fix the roof or buy an umbrella, only you just bought the umbrella.

Regards
Ken.................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------

 

[KLewisBPM]

Ken,

Yeah it was a quick fix i then carried on with my spreadsheet.

I promise I will fix the Roof tommorow!! Because I hear there is thunder storm coming LOL.



Kind Regards

Kelley Lewis

 

[KenWright]

LOL - When it rains it pours you know

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------

 

[gizmo1973]

NP Ken, I'm feeling much better now anyway as you've seen in another area.
Just trying to stay with the ethos of TT all!!

Regards, Phil.

M.U.F.C. Show your true support here:

http://www.shareholdersunited.org/join.php

"Shares not Shirts

 

[KLewisBPM]

=(IF(ISNA(VLOOKUP(B2,ophrs,2,FALSE)),0,(J2+(VLOOKUP(B2,ophrs,2,FALSE)))/I2))

This was my final formula!! and it works great

Thanks agin to all input

Kind Regards

Kelley Lewis