excel search indexing excel-formula excel-2021

Excel search function problem when trying to display only certain columns

[Office Professional 2021] I have an Excel workbook made of 2 sheets:

--1st sheet is named Database_locale and it had a table that has data from column A to column L and this database is updated every day so now it has about 12000 rows but it will increase over time.

--2nd sheet is called Ricerca and it has a cell (C6) in which i can type anything but i'm looking for names in this case. In a separate cell in this same sheet i have the formula that actually looks up the the values and returns them.

What it needs to do is look for all the cells in sheet Database_locale in column B (where all the names are) and return all the rows that match the name i looked for.

I tried this first:

=FILTER(Database_locale!A2:L1000000; ISNUMBER(SEARCH("*"&C6&"*"; Database_locale!B2:B1000000)))

Which works and returns all the columns from A to L and all the rows that match as expected.

Now what i'm trying to do is to display only some of the columns (1,2 and 5) to get rid of some unnecessary data so i used the index function like this:

=INDEX(FILTER(Database_locale!A2:L1000000; ISNUMBER(SEARCH("*"&C6&"*"; Database_locale!B2:B1000000)));; 1)

Which works fine and returns only column 1.

The problem is that if i add any more columns to display it doesen't work as expected. I tried:

=INDEX(FILTER(Database_locale!A2:L1000000; ISNUMBER(SEARCH("*"&C6&"*"; Database_locale!B2:B1000000)));; {1;2;5})

=INDEX(FILTER(Database_locale!A2:L1000000; ISNUMBER(SEARCH("*"&C6&"*"; Database_locale!B2:B1000000)));; [1;2;5])

Both give different errors or partially work but in a very strange manner, i've tried many more combinations but listing every one and explaining what they do would be very long to read.

I could just use the formula that diaplays one column correctly and add one for every other column that i need to display but the sheet becomes sluggish.

Probably i'm doing a silly mistake and can't notice it but i've tried for hours and can't get it to work in a single formula to display all the columns that i need.

This is what i'm looking for

Thanks in advance and sorry for my english!

Solution

There are quite a few ways to accomplish the desired output, I will try to outline the ways as far I know, which will work with the version ^{Excel 2021} specified in the post:

• Formula used in cell J6

=LET(
     _Data, A2:F29,
     _SearchKey, I5,
     _SerchCol, INDEX(_Data,,2),
     _Output, FILTER(_Data,1-ISERR(SEARCH("*"&_SearchKey&"*",_SerchCol)),""),
     FILTER(_Output, 1-ISNA(XMATCH(A1:F1,J5:L5))))

The above formula definitely quite large because I have used LET() function to define variables and make it easier to read using them.
_Data variable refers to the original source (Better suggestive is to convert the range/list into Structured References aka Tables).
_SearchKey is the cell defined for the search word that needs to be searched in the specific column of source.
_SearchCol is the column where the above needs to looked at.
Next using SEARCH() function (although the wildcards are not required here) to get the position of the text, and we can use ISNUMBER() or 1-ISERR() as well which does the same by returning 1 for TRUE while 0 for FALSE.
Using the above within FILTER() function as an include parameter gives the output. However to get the desired output with the respective columns one can make it dynamic by getting the headers from the original source. This is achieved with a combination of 1-ISNA() or ISNUMBER() combination with XMATCH() which returns 1 and 0 as well to get the Final_Output
The above formulas can be made more generic in the following ways. I have defined variables to make it easier to read if one is trying to debug.

Alternative Option 1:

=FILTER(FILTER(A2:F29,1-ISERR(SEARCH("*"&I5&"*",B2:B29))),{1,1,0,0,1,0})

Note: In the above formula since we are aware of the columns required for the output hence using a combination of 1 & 0 as for the one which is required and zero for the not required --> {1,1,0,0,1,0} columns can be simplified but it is explicit and not dynamic like the one in above which uses 1-ISNA()+XMATCH().

Alternative Option 2:

=LET(
     _Data, FILTER(A2:F29,1-ISERR(SEARCH("*"&I5&"*",B2:B29))),
     _Rows, SEQUENCE(ROWS(_Data)),
     INDEX(_Data,_Rows,{1,2,5}))

The above uses SEQUENCE()+ROWS() function for indexing of the rows and explicitly using {1,2,5} for the columns to get the desired output.

Alternative Option 3:

=FILTER(CHOOSE({1,2,3},A2:A29,B2:B29,E2:E29),ISNUMBER(SEARCH("*"&I5&"*",B2:B29)))

Note: The use of CHOOSE() function for the required columns to output.

Another Option 4:

Will be dynamic as the first one, using MMULT()

=FILTER(FILTER(A2:F29,1-ISERR((SEARCH("*"&I5&"*",B2:B29)))),MMULT({1,1,1},N(A1:F1=TRANSPOSE(J5:L5))))

A glimpse of using dynamic formulas:

Notes: Since you are working with large amount of datasets, it is suggestive and recommended to convert the source range into Structured References aka Tables so which helps in resizing automatically whenever there is new rows/columns of data added/removed, using the whole range of the excel will certainly slow down the working functionality of Excel.