Formula for verifying whether the value in three cells are #N/A or a numeric value

[SemiCatS]

Hi.

I've got three columns which values are determined by the VLOOKUP formula. Now I would like to create a fourth column, in which
the three before-mentioned columns are "verified". IF one of the three columns cointains a valid (numerical value picked up by the
VLOOKUP formula) then this value should be shown in the cell. IF all the three columns contains #N/A, the cell should say #N/A.

I've tried this: =IF(AND(B2="#N/A";C2="#N/A";D2="#N/A");"#N/A";"OK")

This formula should (?) return "OK" if a valid value is found and "#N/A" if not, but it doesn't work.lp
Second, I have no idea how to make it return the valid number - if found - instead of "OK".

I couldn't figure out how to attach the excel file in question, so I pasted the link to the dropbox-location below.

https://www.dropbox.com/s/2gz1hi0q1dxaa8x/TC_ID+ALT_ID.xlsx?dl=0

Any help is greatly appreciated.

Best regards

Stig M. Thu

 

[SemiCatS]

Thank you all for giving me hand here. I'm now able to do the verification part, but a collegue of mine just made me aware that the VLOOKUP-formula isn't catching everything it should in the
three columns, leaving me with a large number of "#N/A"s that shouldn't be. I found a "workaround" which required me to go through ALL the >2000 lines and hit F2 and then Enter in all the
cells in the A-column in the MAIN-sheet... but I'm quite sure there must be an easier way to do this? I suspect it might have something to do with the formatting (I've had to format a lot of the
numbers as text, since they're in all kinds of strange shapes and sizes, like 68 1234 123, or 123 456 789, or just plain 1234567, and some are even plain text) Any suggestions on how to avoid
having to hit F2 + Enter a "zillion" times the next time I bump into the same thing?

Best regards

Stig M. Thu