Google sheets - find the cell containing specific text

1
votes

In Google Sheets, using formulae, is there a way I can search for a certain string in a certain sheet, and return the reference of the cell which contains that string?

The string would be unique to that cell, but the data isn't stored in a list, nor is it sorted

1
google-sheetsgoogle-sheets-formula

1 Answers

1
votes

You can combine FIND or SEARCH (which is case insensitive) into LARGE and ARRAYFORMULA functions:

=ARRAYFORMULA(ADDRESS(LARGE(ISNUMBER(SEARCH(G1,A1:F15))*ROW(A1:F15),1),LARGE(ISNUMBER(SEARCH(G1,A1:F15))*COLUMN(A1:F15),1)))

enter image description here

If search string is not unique in range, it will find last cell that contains string. If you want find first cell, change LARGE to SMALL function.