SOLVED

Using IF(AND) inside TEXTJOIN array -- why is this not working??

%3CLINGO-SUB%20id%3D%22lingo-sub-2065733%22%20slang%3D%22en-US%22%3EUsing%20IF(AND)%20inside%20TEXTJOIN%20array%20--%20why%20is%20this%20not%20working%3F%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2065733%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there!%20I'm%20trying%20to%20filter%20and%20show%20selected%20results%20from%20a%20table%20on%20another%20sheet%2C%20based%20on%203%20parameters%20(something%20that%20is%20insanely%20easy%20on%20googledocs%20with%20the%20filter()%20function%2C%20which%20doesn't%20seem%20to%20be%20available%20with%20my%20version%20of%20Excel).%20I%20am%20using%20the%20TEXTJOIN%20function%2C%20which%20works%20when%20I%20use%20a%20single%20condition%2C%20but%20when%20I%20try%20to%20nest%20IF%20statements%20or%20use%20IF(AND)%2C%20it%20doesn't%20work!%20Any%20idea%20why%3F%20Is%20it%20not%20capable%20of%20calculating%20based%20on%20several%20parameters%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20my%20formula%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3DTEXTJOIN(%22%20%2C%22%2CTRUE%2CIF(AND(Records!D%246%3AD%246000%3D%24C%2415%2CRecords!I%246%3AI%246000%3DK%243%2CRecords!B%246%3AB%246000%3DJ%243)%2CRecords!%24C%246%3A%24C%246000%2C%22%22))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20confirmed%20that%20it%20works%20with%20each%20of%20the%20AND%20conditions%20separately%2C%20but%20it%20gives%20the%20null%20value%20when%20I%20use%20them%20together.%20I've%20spent%20hours%20on%20this%20simple%20problem%20and%20want%20to%20be%20done%20with%20it!%20I'd%20appreciate%20any%20advice.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2065733%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2065787%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20IF(AND)%20inside%20TEXTJOIN%20array%20--%20why%20is%20this%20not%20working%3F%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2065787%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F931000%22%20target%3D%22_blank%22%3E%40tashaflies%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUsing%20AND()%20you%20try%20to%20find%20values%20which%20at%20the%20same%20time%20are%20equal%20to%26nbsp%3B%3CSPAN%3E%24C%2415%2C%20K%243%2C%20J%243.%20Obviously%20you%20have%20no%20such.%20Perhaps%20you%20mean%20OR%20condition%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi there! I'm trying to filter and show selected results from a table on another sheet, based on 3 parameters (something that is insanely easy on googledocs with the filter() function, which doesn't seem to be available with my version of Excel). I am using the TEXTJOIN function, which works when I use a single condition, but when I try to nest IF statements or use IF(AND), it doesn't work! Any idea why? Is it not capable of calculating based on several parameters? 

 

Here's my formula: 

=TEXTJOIN(" ,",TRUE,IF(AND(Records!D$6:D$6000=$C$15,Records!I$6:I$6000=K$3,Records!B$6:B$6000=J$3),Records!$C$6:$C$6000,""))

 

I have confirmed that it works with each of the AND conditions separately, but it gives the null value when I use them together. I've spent hours on this simple problem and want to be done with it! I'd appreciate any advice.

 

Many thanks!

4 Replies

@tashaflies 

Using AND() you try to find values which at the same time are equal to $C$15, K$3, J$3. Obviously you have no such. Perhaps you mean OR condition?

Best Response confirmed by tashaflies (New Contributor)
Solution

@tashaflies

AND returns a single TRUE or FALSE value, not an array. Try

 

=TEXTJOIN(" ,",TRUE,IF((Records!D$6:D$6000=$C$15)*(Records!I$6:I$6000=K$3)*(Records!B$6:B$6000=J$3),Records!$C$6:$C$6000,""))

 

Confirm the formula with Ctrl+Shift+Enter to turn it into an array formula, otherwise it won't work as intended.

@Hans Vogelaar AH THANK YOU! This works. I'm endlessly grateful. 

@Sergei Baklan Of course, that makes sense. But I do need all 3 conditions to be true, so I don't think OR() would work. But the other solution offered below works, hallelujah! Thank you!