Search code examples
google-sheetsgoogle-sheets-formula

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

Solution

  • 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.