Search range for cell containing a string then return last character from that address

Question

I'm trying to make formula that will return the last character from a cell that contains a specific word.

For that I need to find the cell.

I tried

=IF(
  COUNTIF(B$3:V$23;
    CONCATENATE(B27; "*"));
  RIGHT(B$3:V$23);
  0)

Answer 1

Find Cells that Contain a String

Two case insensitive formulas that search within a range for any cells that include a specific text string:

• Formula 1: Returns cell addresses
• Formula 2: Returns cell addresses & last character

Returns Cell Addresses

=IFERROR(
  SORT(QUERY(TOCOL(
    BYROW(A1:C100, LAMBDA(r, 
      BYCOL(r, LAMBDA(c, IFERROR(
        ADDRESS(ROW(r)/REGEXMATCH(c, "(?i)"&E2)/(LEN(E2)>0),
          COLUMN(c))))))),1))), "No Matches!")

Return Cell Addresses & Last Character

=IFERROR(SORT(
  QUERY(ARRAYFORMULA(SPLIT(TOCOL(
    BYROW(A1:C1000, LAMBDA(r, 
      BYCOL(r, LAMBDA(c, IFERROR(
        CELL("address", c) &"➡️"& LOWER(RIGHT(TRIM(c),
         1/(1/REGEXMATCH(c,"(?i)"&E2))/LEN(E2)>0)))))
    )), 1), "➡️")))), "No Matches!")

Answer 2

You want to return a cell address based on an IF statement.

Use the CELL function in your IF results. The ADDRESS function could also be an option.

• Cell address (Absolute): =if(B1="abc",cell("address",B1),"no match")
• Cell Column: =if(B1="abc",cell("col",B1),"no match")
• Cell Row: =if(B1="abc",cell("row",B1),"no match")
• Address (Relative): =address(B6,B5,4,true)

---

SAMPLE