CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
jonnybolton16
jonnybolton16

Reputation: 232

Google sheets - find the cell containing specific text

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

Upvotes: 1

Views: 5717

Answers (2)

gieoon
gieoon

Reputation: 177

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.

Upvotes: 0

basic
basic

Reputation: 11968

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.

Upvotes: 3

Related Questions