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

[Rudrabhadra]

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.

[#2]

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

[#3]

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

[#4]

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

[#5]

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