Excel search function problem when trying to display only certain columns

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.