SOLVED

TEXTJOIN/Nested IF error

Copper Contributor

Ok, So I’m almost complete with this simple excel database I created to help make my job more efficient. The only issue I am running into is creating an accurate TEXTJOIN function. The conditions and strings I have included seems to be accurate however excel keeps giving me an error of either “too many argument”, or just plain error..

 

In my coding I need some guidance on the 124 TAB to communicate with the 124DS tab within the workbook. The cells within that tab need to work in unison with each other. The condition I am trying to create for EX. each store (ex. 1146) should copy all trailer numbers onto the 124DS if the condition of “H” is true. As you can see I have the worksheet communicating with each other already. HOWEVER I sometimes can have more than one “H” trailer for the same store which is why I was trying to add in a JOIN statement. I also tried with concatenate func but that was to no avail either.

 

This is what I’ve been trying to run with no luck:

=TEXTJOIN(“   ,   “,TRUE, IF('124'!K6="H",'124'!I6, IF('124'!N6="H",'124'!L6, IF('124'!Q6="H",'124'!O6,""))))

 

ANY HELP WOULD BE appreciated; I just know the solution is simple I just don’t know where my error is. I placed 2 “H” trailers in 1146 box on worksheet 124 to run the conditions on 124DS in its corresponding 1146 location in 124DS.

4 Replies

@Optimistic92 

=TEXTJOIN(" , ",TRUE,IF('124'!K6:Q6="H",'124'!I6:O6,""))

 

Hi, thanks for your response the string you provided is one that I initially used and excel returned #VALUE!

The one I currently have keyed is this:
=TEXTJOIN(" , ",TRUE,IF('124'!K6="H",'124'!I6,IF('124'!N6="H",'124'!L6,IF('124'!Q6="H",'124'!O6,""))))

Although it is returning the value from cell I6 it is not including the other "H" value from N6, this is the main issue I am encountering, its only giving me a partial return.
best response confirmed by allyreckerman (Microsoft)
Solution

@Optimistic92 

I guess you are on an old version of Excel. Since this is an array formula you have to use the magic keystroke CRTL-SHIFT-ENTER.

 

Thank You,
Not how I expected but the CRTL-SHIFT-ETR function did work. Apparently something I hadn't know is Excel will not allow you to enter an array formula over merged cells. This is certainly helpful for the time being though, Thank you!
1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@Optimistic92 

I guess you are on an old version of Excel. Since this is an array formula you have to use the magic keystroke CRTL-SHIFT-ENTER.

 

View solution in original post