A doosy- Complex logic for filtering - Can you figure it out?

I have been trying to figure this out for days, but I just cannot get everything to work. I can get some scenarios to work, but not all at the same time. It is very bazaar and frustrating. Even ChatGPT can't get it right.

Please download and look at the workbook in the attached zip file. It will make what is needed much easier to understand.

Setup:

• I have a worksheet called "Libraries" with a listing of sample libraries, with columns for (A)Vendor, (B)Library Name, (C)Library Subgroup, and (D)Patch Name.
  
• I have a "Report" worksheet where I am doing filtering on the data in the Libraries sheet.

• Ok, on the Report sheet I have 3 cells, C11 to E11 which are for inputting text to filter by. C11 filters over column Libraries!B, D11 over Libraries!C, and E11 over Libraries!D. The filter restricts the result listing more as more of cells C11 to E11 get text to filter on, as in the resulting list has to contain only records from Libraries that match all of the specified text as per the corresponding column. I hope that makes sense.
• Also on the Report sheet is cell C13 which is for specifying filter text which should be a match if ANY of the 3 columns in Libraries contains the specified text.
  

• Finally on the Results sheet is the cell (C17) containing the filter formula, which becomes the first row of a column of the resulting filtered list. There are other columns that are similar but for simplicity just focus on getting C17 to work putting correct results in that column. Also, ignore the other text cells on the Report sheet.

This formula below (in cell C17) seems to work for every scenario of these inputs except when there is text only in C13, in which case it is like that text is ignored - I basically just get as the result the entire list as if no filter text has been specified.
But if I change the '+' before the IF to a '*', then the lone text in cell C13 works, but other scenarios do not.

The formula I have now:

=IFERROR(
FILTER(
Libraries!B2:B500000,
(
( ISNUMBER(SEARCH(LOWER(Report!C11), LOWER(Libraries!B2:B500000))) *
ISNUMBER(SEARCH(LOWER(Report!D11), LOWER(Libraries!C2:C500000))) *
ISNUMBER(SEARCH(LOWER(Report!E11), LOWER(Libraries!D2:D500000))) )
+
IF(Report!C13<>"",
( ISNUMBER(SEARCH(LOWER(Report!C13), LOWER(Libraries!B2:B500000))) +
ISNUMBER(SEARCH(LOWER(Report!C13), LOWER(Libraries!C2:C500000))) +
ISNUMBER(SEARCH(LOWER(Report!C13), LOWER(Libraries!D2:D500000))) ) )
) > 0

),
""
)

If you can solve it you will be forever known as a genius.
PLEASE SOLVE IT!
Thanks.

Ahich version of Excel are you using?

Please add the blue part in the formula of B17 and it will work:

Please Login or Register  to view this content.

I am using Office Excel 2016, but it might be good if it could work on some earlier versions, but that's not at all critical.

EDIT: I am using Office Pro 2021.

I am using Office Excel 2016

I don't think so you are using FILTER, but Excel 2016 does not have FILTER.
You can check your version in Excel: Click on your account name (at the top) and then click Office user info. It should be 2019, 2021 or 365.

Did you download the example workbook?

But your file is too big and not very manageable. Please provide a sample sheet with a maximum of a few tens of rows, so that you can test it much more easily. I will return such a workbook with a working formula

Thanks for the tip on finding the version. I didn't know that was there.

As for the workbook I attached, it's less than 3Mb, so I don't understand the problem with it. It's about as small and simple as I can get it.

it's less than 3Mb

3 Mb is not handy here.
I have reduced your file size to approximately 120 KB. That is handy, and can be uploaded here without zipping.
For some reason your file had a lot of empty rows that somehow took up a lot of space.

Here is an uploaded file with the adjusted formula, which I think works well.
If results are different than expected, I would like to hear about it.

Thanks for that.

That seems to work for the various filters. However if no filters are specified the full list should be the result. This result is an empty list when there are no filters.

For extra credit you can have a go at the "Exclude" text field in D13. The idea here is to exclude from the results list any records that have the specified text in any of the columns B, C, or D in the Libraries data.

Thanks for your assistance!

Which version of MS-Office are you using and please adjust this in your profile.

Then we can take that into account.

For extra credit ...

I still have to receive the first credit .

I am in Office Pro 2021.

As for the credit, well... the CONCAT is not doing the job because if you put text in two or more of C11 to E11 you get a string that isn't likely to exist in any of the fields. C11 pairs with Column B in Libraries, D with C, and E with D. Those keywords cannot be joined together for that part of the filtering.

Thanks for updating your profile.

if no filters are specified the full list should be the result

I have created a formula that returns the result of all the columns at once.
To ensure that everything is returned in case no filter is used, I added the following: ( CONCAT(C11:E11,C13)="" ) * ( Libraries!A2:A22 <> "") +

In addition, I have removed unnecessary parts for readability:
- LOWER is not necessary, because SEARCH is not case sensitive
- IFERROR is not needed, because FILTER has its own parameter in case nothing is found
- IF .. > 0 is not necessary, because any result > 0 is returned.
Please empty the whole table and try in B17:

Please Login or Register  to view this content.

the CONCAT is not doing the job because if you put text in two or more of C11 to E11 you get a string that isn't likely to exist in any of the fields

Did you try it???
The string C11:E11 is not compared to fields in your table. It only checks whether all three fields are empty or not.
I have also tested with multiple fields filled in and that works well.
If something doesn't work, please show it in an example.

Thanks very much, HansDouwe the Genius!

That does now seem to be doing everything correctly. Much appreciated! I presume this will work in versions of Excel after 2016?

Now do you want to try for the extra credit?

FILTER is available in Excel 2021 and Excel 365.

exclude from the results list any records that have the specified text in D13 in any of the columns B, C, or D in the Libraries data

Please add the blue parts to the formula

Please Login or Register  to view this content.

Originally Posted by HansDouwe

Please add the blue parts to the formula

Please Login or Register  to view this content.

That seems to work perfectly.

So, 2 questions:

1. Do you think there is a way to make this operate faster?
2. I wonder if you might know how this might be changed to work in some versions of Excel prior to 2021 and 365, if possible. It would be good if this was more portable to earlier versions. Maybe it would only work if coded in VBA. What do you think?

Also, I intend to make this available to the composer community, and if I do, how would you like to be credited with your genius contributions?

Thanks!