AkelaWolf76
New Member
- Joined
- Aug 15, 2023
- Messages
- 10
- Office Version
- 365
- Platform
- Windows
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.
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.