TEXTJOIN Array using IF with nested AND

[SjMaxwell]

I have a list with Date, Unit#, and Error Code. I am trying to use TEXTJOIN to pull every Error code in the list that has the same Unit# and Date.
I am using an array formula and I did Ctrl+Shift+Enter.

This formula works, it outputs all error codes for the unit
{=TEXTJOIN(",",TRUE,IF(List!B:B="unit1",List!C:C,""))}

This formula works, it outputs all error codes for the date
{=TEXTJOIN(",",TRUE,IF(List!A:A="Feb 1",List!C:C,""))}


This is my formula that isn't working
{=TEXTJOIN(",",TRUE,IF(AND(List!B:B="unit1",List!A:A="Feb 1"),List!C:C,""))}

I'm guessing it's a limitation on the AND formula being able to use an array. Anybody have any suggestions? Please and thanks!

[Glenn Kennedy]

1. Please read the yellow banner and attach a small sample sheet.

2. please update your profile. If you have TEXTJOIN, you are no longer using Excel 2016 or earlier.

[SjMaxwell]

Sorry, that was the one I had when I join the forum. I'll get right on that. Any suggestion for my formula issue?
Thanks

[CARACALLA]

Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

[SjMaxwell]

see attached

[AliGW]

In B2 copied across and down:

=IFERROR(TEXTJOIN(",",,FILTER(List!$C$2:$C$10,(List!$A$2:$A$10=B$1)*(List!$B$2:$B$10=$A2))),"")

[SjMaxwell]

FILTER doesn't seem to be a formula in 2019.

[AliGW]

Ah! Sorry - forgot you have 2019 and not 365.

[Glenn Kennedy]

Use this:

=TEXTJOIN(", ",,IF(List!$B$1:$B$100=$A2,IF(List!$A$1:$A$100=B$1,List!$C$1:$C$100,""),""))

DO NOT use whole column references. Use something sensible, but future-proof.

You seem to be using an older version of Excel than me. So, please refer to the attached file. If the formulae are enclosed within a pair of { }, these are array formulae.

These are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[SjMaxwell]

thanks, that works. I was really stuck on wanting to use that AND instead of thinking around the problem differently.

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

[Sanjubhk]

THIS IS JUST A LITTILE TRY TO JOIN TEXT ON CODITIONAL BASIS IN EXCEL

Please Login or Register  to view this content.

Mod Note: You need to wrap code in code tags here - I have done it for you this time.