How to CONCATENATE Nested IF formula without premature stopping

[NautiWill]

This may just be a quirk of Excel, but I can't force Excel to complete all parts of a Concatenate Formula of nested IF functions.

I've attached a file but the summary is I want to lookup three separate values from one cell and concatenate them all together.

The problem is once excel finds one 'result' in this case a text "OPEN", then it will finish looking for any others.

If there are no blanks then it works fine.

Any advise is awesome!

Capture.PNG

Formula is;
=IF(OR(A4="NIL",A4=""),"",IF(VLOOKUP(LEFT(A4,16),D4:E7,2,FALSE)="","OPEN",TEXT(VLOOKUP(LEFT(A4,16),D4:E7,2,FALSE),"dd mmm yyyy")&" "&IF(VLOOKUP(MID(A4,30,16),D4:E7,2,FALSE)="","OPEN",TEXT(VLOOKUP(MID(A4,30,16),D4:E7,2,FALSE),"dd mmm yyyy"))&" "&IF(VLOOKUP(MID(A4,59,16),D4:E7,2,FALSE)="","OPEN",TEXT(VLOOKUP(MID(A4,59,16),D4:E7,2,FALSE),"dd mmm yyyy"))))

[kvsrinivasamurthy]

In B10. Note the changes

Please Login or Register  to view this content.

[NautiWill]

Thank you so much for the solution, it has made my day even if I don't quite understand how this worked while brackets and CONC functions all failed to!

But, it works, so who cares now right!!!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[NautiWill]

Sorry, didn't realise!! Have done now.