Forum Discussion

Rudrabhadra's avatar
Rudrabhadra
Brass Contributor
Oct 23, 2023

Find the column and row number in an excel spread sheet based on criteria.

Hi Good day,

I am looking for a way to find the address of a cell based on a criteria.  I am having specific data placed randomly in sheet 1 of a workbook in different columns and rows. (like Apple, orange, berries etc.). In sheet 2 of the workbook I need to get to know the cell address of the specific text  when I type in the specific data. (For example when I type  "Apple" in  sheet-2 I want to know the address of the cell). Appreciate if anyone could help. Thanks in advance.

excel
Formulas and Functions

4 Replies

Sort By
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Oct 29, 2023

    Rudrabhadra 

    A top-down approach that will return multiple matches

    = LET(
    address, ADDRESS(ROW(matrix), COLUMN(matrix), , 0),
    matches, IF(matrix=target, address, NA()),
    TOROW(matches, 3)
  )
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Oct 28, 2023

    Rudrabhadra 

    =ADDRESS(MIN(IF(sheet1!A1:H100=J1,ROW(1:100))),MIN(IF(sheet1!A1:H100=J1,COLUMN(A:H))))

    An alternative with e.g. Excel 2013 could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021. The search value is in cell J1 and the range in sheet1 is A1:H100 in this example. The range in sheet1 can be adapted as required.

     

     

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Oct 23, 2023

    Rudrabhadra 

    This is the idea:

    matrix being the range where the term might be found.

    =LET(
    rows, ROWS(matrix),
    cols, COLUMNS(matrix),
    Locate, N(matrix = "Apple"),
    r, SUM(Locate * SEQUENCE(rows)),
    c, SUM(Locate * SEQUENCE(, cols)),
    IFERROR(ADDRESS(r, c, 4), "Not found")
)

     

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Oct 23, 2023

    Rudrabhadra 

    Sub findstring()

Dim cell, bereich As Range
Dim i As Long

Set bereich = Sheets("sheet1").Range("A1:H100")
For Each cell In bereich
For i = 1 To 3
If cell.Value = Cells(i, 12).Value Then
Cells(i, 13).Value = cell.Address
Else
End If
Next i

Next cell

End Sub

    This code returns the position of a search string within the range A1:H100 in sheet1. A search string can be entered in cell L1, L2 and L3 of sheet2 and the address of the search string is returned in cell M1, M2 and M3. The code performs an exact match.

Resources