Jan 15 2021 12:20 PM
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!
Jan 15 2021 12:32 PM
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?
Jan 15 2021 12:40 PM
SolutionAND 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.
Jan 15 2021 12:48 PM
@Hans Vogelaar AH THANK YOU! This works. I'm endlessly grateful.
Jan 15 2021 12:49 PM
@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!
Jan 15 2021 12:40 PM
SolutionAND 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.