CODEWITHSUNDEEP
google-sheetsspreadsheetformulas
gavsiu
gavsiu

Reputation: 757

Search for a particular cell in Google Spreadsheets and return the row number

For instance, I have a bunch of categories with 1 on each row and each category has 1 or more data on their own column. Given a string, I want to find which category it belongs to.

         A    |     B     |     C     |     D
1    CARS     |  Civic    |           |
2    TRUCKS   |  F-150    |  F-650    |  F-750
3    PLANES   |  747      |  F/A-18   |

Given 747, I want to know that it is from row 3 or that it is a plane or that F- is a truck.

I've tried using several functions, including vlookup, filter, match, etc, but couldn't get them to work.

Is it possible to do this without scripts?

Upvotes: 0

Views: 292

Answers (1)

AdamL
AdamL

Reputation: 24609

Assuming the data is in columns A to E (this could be extended), and that the search term is in F1, and the search term must start the string, and that all applicable matches will be returned, try:

=IF(LEN(F1),FILTER(A:A,COUNTIF(IF(REGEXMATCH(B:E&"","^"&F1),ROW(A:A)),ROW(A:A))),)

Upvotes: 1

Related Questions