CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Falcon4ch
Falcon4ch

Reputation: 120

What is the best alternative to FILTER if you need to have multiple rows for the condtion?

I have a sheet that looks like this:

enter image description here

I would like to be able to return all results from row 1 given a criteria from the data in B2:G7. For example, if I wanted "Grass" from the above sheet, the results would be:

enter image description here

I have tried to do this with FILTER, but it requires the condition be limited to one row or column. What would be the best alternative?

Example sheet here: https://docs.google.com/spreadsheets/d/110De2u6mKLT7SOTfaXN6EZVqgD35pEeeog9o5WWn5bs

Upvotes: 0

Views: 1038

Answers (1)

Erik Tyler
Erik Tyler

Reputation: 9355

I've added a new sheet ("Erik Help") to your sample spreadsheet. Enter a search word in A2. The results will be returned beginning in C2 and filling rightward.

The C2 formula:

=IFERROR(FILTER(Sheet1!B1:G1,ISNUMBER(SEARCH(TRIM(A2),QUERY(Sheet1!B2:G,,ROWS(Sheet1!B2:G))))),"NO MATCHES")

The important piece here is the QUERY. While most people know that the final parameter of a QUERY can be set to 0 or 1 to signify how many header rows to return, the actual number of rows that can be returned in the header is not limited to 0 or 1. So if we ask QUERY to return no selected data in a "Select" clause and yet set the header parameter to the number of data rows, headers for each column wind up being a column-by-column mash-up of the data from each row in each column.

This we can use with FILTER, as you can see.

Upvotes: 2

Related Questions