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

 

[arthurbr]

You can check the presence of a non error value with =IF(SUM(IF(ISERROR(B3:D3),1))=3,"#N/A","ok"). Then pull down as needed

This is an array formula to be committed with Ctrl+Shift+Enter

To extract the value, I haven't found a solution yet

 

[RoryA]

For the first part:
=IF(COUNTIF(B2:D2,"<>#N/A"),"OK","#N/A")

for the second:
=LOOKUP(REPT("Z",255),B2:D2)
will return the last non-error value in the range, or #N/A if they are all errors.

 

[tygrrboi]

I went with this...

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'}span.s1 {color: #006107}span.s2 {color: #0057d6}span.s3 {color: #ab30d6}span.s4 {color: #a54a29}</style>=IF(COUNTIF(B2:D2, "#N/A") = 3, NA(), IFERROR(B2,"")&IFERROR(C2,"")& IFERROR(D2,""))

 

[Rick Rothstein]

This normally entered formula should work...

=IF(SUM(B2:D2),"OK",NA())

 

[RoryA]

I guess you didn't download the file, Rick? I was going to suggest COUNT based on the title of the question, but the returned values are not actually numbers, they are things like D000502052-001

 

[Rick Rothstein]

I guess you didn't download the file, Rick?

No, I didn't. I think this formula might work though...

=IF(LEN(B2&C2&D2),"OK",NA())

 

[RoryA]

That would only return OK if all three cells contain a valid value, not if only one of them does.

 

[Rick Rothstein]

That would only return OK if all three cells contain a valid value, not if only one of them does.

I completely misread the original question (skimmed it too fast I guess)... I just reread it and realized I was trying to answer a question that was not asked.

What about this formula then...

=IF(COUNTIF(B2:D2,NA())=3,NA(),"OK")

 

[SemiCatS]

Hi.
Thank you for the formula. I've never even heard of array formulas before, but it worked like a charm (Just had to replace any , with ; to make it work with my Norwegian setup of things)
This is going to save me a lot of manual labour and time. Wish you a nice week-end.

Best regards

Stig M.