Textjoin search a range with multiple criteria

[purgamentum]

Not sure why this isn't working:

Please Login or Register  to view this content.

Looking to search the range that contains both "REV" AND "LAB".
Result is just a blank cell. Any help greatly appreciated.

Book2 - Copy.xlsx

[tanasedn]

Hi,
please attach a sample file with data. It will be much easier to us to find a solution

[purgamentum]

Hi,
please attach a sample file with data. It will be much easier to us to find a solution

Thanks; added a file to the top post.

[nicnaws]

Hello! If you are looking to return a horizontal list of results that contain both "REV" and "LAB", you can use the formula below. All are in the "Income" category, and the results you're looking for (REVDWLAB, REVINLAB, REVSSLAB, REVTBLAB, REVTFLAB, REVWPLAB, REVMSLAB, REVSALAB, REVFILAB, REVMPLAB, REVFALAB) should be in column A. I hope this helps!

=TEXTJOIN(", ",TRUE,FILTER(A:A,ISNUMBER(SEARCH("*REV*"&"*LAB*",A:A))))

[JEC.]

Small change. Office 2019 does not have the FILTER function.

Please Login or Register  to view this content.

[tanasedn]

Just a little adjustment om your formula

Please Login or Register  to view this content.

[nicnaws]

Thank you for the modification. I didn't realize that.

[Rick Rothstein]

JEC's formula would work (remember, the formula must be array-entered) if the range was set to match the range you used, but I would combine his concatenation into a single text string...

Please Login or Register  to view this content.

[JEC.]

In VBA it would be easy with a double Filter

Please Login or Register  to view this content.

[purgamentum]

Sorry for the delay; busy with other work. Thank you all very much! All 3 of those are working for me

[Rick Rothstein]

In VBA it would be easy with a double Filter

Please Login or Register  to view this content.

That line of code does not guarantee that "REV" will always occur before "LAB" (which is what I understood the OP to want).

[JEC.]

Correct, I understood just containing both... nevertheless, it's solved

[purgamentum]

Correct, I understood just containing both... nevertheless, it's solved

True; I didn't need them to be in order, thank you all!

[tanasedn]

If the solutions offered helped you to solve your problem, then mark the thread as SOLVED (thread tools in the top menu) and you can click on * to add reputation to those who helped you, as a way of saying thank you !

[purgamentum]

If the solutions offered helped you to solve your problem, then mark the thread as SOLVED (thread tools in the top menu) and you can click on * to add reputation to those who helped you, as a way of saying thank you !

I marked it solved days ago; here's your email notification, thread says solved in the subject:
Attachment 817098