If And formula not working

[Janger30]

Hi. I have an issue with a formula that doesn’t seem to be working. It was working until I added the AND statement to include the F = Y part. I need it to look in two separate columns, one with a simple yes or no and one with a search within text, then give me the joined list of correct results.
Any ideas, hopefully I am just being a bit stupid and missing the obvious.
=TEXTJOIN(“;”,TRUE,IF(AND(Consultees!F2:F46=“Y”,ISNUMBER(SEARCH(A2,Consultees!E2:E46))),Consultees!C2:C46,””))

 

[SanjayGMusafir]

=TEXTJOIN(“;”,TRUE,IF(AND(Consultees!F2:F46=“Y”,ISNUMBER(SEARCH(A2,Consultees!E2:E46))),Consultees!C2:C46,””))

Check this and revert -

=IF(AND(Consultees!F2:F46=“Y”,ISNUMBER(SEARCH(A2,Consultees!E2:E46))),TEXTJOIN(“;”,TRUE,Consultees!C2:C46,””))

 

[Peter_SSs]

Check this and revert -

Wondering if you checked it?

@Janger30
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Assuming that you have the FILTER function, try this

=TEXTJOIN(";",TRUE,FILTER(Consultees!C2:C46,(Consultees!F2:F46="Y")*(ISNUMBER(SEARCH(A2,Consultees!E2:E46))),""))

 

[SanjayGMusafir]

Wondering if you checked it?

In fact @Peter_SSs I had no way to check it or test it. I relied totally on what OP gave and modified it.

 

[Peter_SSs]

In fact @Peter_SSs I had no way to check it or test it.

Why not? I tested mine. From the OP's formula the data layout is pretty straight-forward and we were told that without the AND F = Y test the formula worked.
As far as checking it, all you had to do was enter it in a cell and you would have immediately found that it isn't even a valid formula.

 

[Janger30]

Wondering if you checked it?

@Janger30
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Assuming that you have the FILTER function, try this

=TEXTJOIN(";",TRUE,FILTER(Consultees!C2:C46,(Consultees!F2:F46="Y")*(ISNUMBER(SEARCH(A2,Consultees!E2:E46))),""))

Hi. Thanks for this, seems to be working. I don’t think I have come across the filter function before so very handy!
Will update my account, cheers for the tip.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

.. and for updating your details.

 

[KRice]

@Janger30, just to follow up...your original formula attempted to use AND, but AND returns a single result, and you want many results, one for each row in your range reference. You could effectively create the AND condition for each row by multiplying the two arrays together, which gives a final, multi-row array consisting of 1's and 0's. Then when the IF function evaluates that resulting array, 1's are treated as TRUE's and the corresponding values from Consultees!C2:C46 will be returned. Also, the formula you posted appears to use the incorrect quotation marks. Here is a version based on what I described:

=TEXTJOIN(";",TRUE,IF((Consultees!F2:F46="Y")*ISNUMBER(SEARCH(A2,Consultees!E2:E46)),Consultees!C2:C46,""))