I am creating a search box on google sheet using =FILTER function. But my problem is it can only return the value in 1 column which is COL B. I want to know if it can return the data from column A as well? I tried to tweak the forumla but i am only getting errors.

Here's the formula that I created.

=filter(data!A2:B, SEARCH(D2,data!B2:B))

Any inputs will be much appreciated. Thank you

CodePudding user response：

I've duplicated your "Search" sheet (renamed "Erik Help"). The formula there:

=IFERROR(FILTER(data!A2:B, SEARCH(B2,data!A2:A&"|"&data!B2:B)))

All I did was concatenate the two columns you want to search, forming one string. I interposed a pipe symbol to eliminate data bleed. (The pipe symbol could have been any symbol that is unlikely to turn up in your actual data.)