Google sheets - find the cell containing specific text

Question

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

Drome

Answer 1

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)))

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.

Ensilage

Answer 2

You can use MATCH(string_to_find, search_range, 0) to find the string and get the row/column number, then ADDRESS() to find the reference to the cell.

Combined, this looks like:

=ADDRESS(MATCH("Cell Text", A1:A, 0), 3)

Note that MATCH() only takes a single row or column so you need to know this beforehand.

Aquavit