Filter and Search with VSTACK

[AkelaWolf76]

I have 2 tables on separate tabs that I would like to search for any row containing a criteria and return the whole row.

After converting the data to tables I create a new tab just for my search and am using cell B3 as my search criteria text box.

I am using the following formula and no matter the search criteria, I get the "No Result" error message returned despite copying the value from one of the table fields in the first place.

=IFERROR(FILTER(VSTACK(Table1[#ALL],Table2),ISNUMBER(SEARCH($B$3,VSTACK(Table1,Table2)))),"No Result")

When I break it down and just use the VSTACK function, this returns a combined table of all data in Table1 & Table2 but as soon as I add the filter function, it errors and the same with the search function.

I have put the formula into gptexcel and the explanation is what I am expecting but cannot get it to work.
"This formula uses the FILTER function to filter a vertical stack of two tables (Table1 and Table2) based on whether a specific text value in cell B3 is found within each row. The ISNUMBER and SEARCH functions are used to check if the text value in B3 is found in each row, and the IFERROR function is used to handle any errors that may occur. If a match is found, the filtered result is returned. If no match is found, the formula returns "No Result""

Any assistance would be greatly appreciated.

 

[AkelaWolf76]

As you are just using col E try

=LET(v,VSTACK(Table1,Table2),FILTER(v,ISNUMBER(SEARCH($B$3,INDEX(v,,5))),"No Result"))

Ideally, I would like to use the search string for any text in any column as we use multiple criteria depending on who is asking the question of us.

 

[Fluff]

Ok, in that case try

=LET(v,VSTACK(Table1,Table2),FILTER(v,BYROW(v,LAMBDA(br,SUM(--(ISNUMBER(SEARCH($B$3,br)))))),"No Result"))

 

[AkelaWolf76]

Thanks for that, did you try the formula I suggested in post#8?

yes and that has worked.. although I would need a separate formula for each column.

I feel like we're getting somewhere. Your help is greatly appreciated.

 

[Fluff]

although I would need a separate formula for each column.

Why?

 

[AkelaWolf76]

Thanks for that, did you try the formula I suggested in post#8?

yes, and that worked for searching a single column. My larger data set has multiple columns with duplicate data so I would need to search the whole table, not just a single column.

We're getting somewhere and your help is greatly appreciated.

 

[AkelaWolf76]

Ok, in that case try

=LET(v,VSTACK(Table1,Table2),FILTER(v,BYROW(v,LAMBDA(br,SUM(--(ISNUMBER(SEARCH($B$3,br)))))),"No Result"))

Ding ding ding We have a winner.
You are a life saver!!

I wonder why the original formula didn't work though, there was no reason for it not to surely????

 

[Fluff]

You cannot search multiple columns the way you were trying.

 

[AkelaWolf76]

You cannot search multiple columns the way you were trying.

Is there anyway to give Kudos or give feedback on here?

 

[Fluff]

You already have done.
Thanks