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"))))
Bookmarks