If And formula not working

Janger30

New Member
Joined
Mar 11, 2013
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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,””))
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
=TEXTJOIN(“;”,TRUE,IF(AND(Consultees!F2:F46=“Y”,ISNUMBER(SEARCH(A2,Consultees!E2:E46))),Consultees!C2:C46,””))
Check this and revert -

Excel Formula:
=IF(AND(Consultees!F2:F46=“Y”,ISNUMBER(SEARCH(A2,Consultees!E2:E46))),TEXTJOIN(“;”,TRUE,Consultees!C2:C46,””))
 
Upvote 0
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
Excel Formula:
=TEXTJOIN(";",TRUE,FILTER(Consultees!C2:C46,(Consultees!F2:F46="Y")*(ISNUMBER(SEARCH(A2,Consultees!E2:E46))),""))
 
Upvote 0
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.
 
Upvote 0
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
Excel Formula:
=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.
 
Upvote 0
You're welcome. Thanks for the follow-up. :)

.. and for updating your details. (y)
 
Upvote 0
@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:
Excel Formula:
=TEXTJOIN(";",TRUE,IF((Consultees!F2:F46="Y")*ISNUMBER(SEARCH(A2,Consultees!E2:E46)),Consultees!C2:C46,""))
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top