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,...
excel
Formulas and Functions
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")
)

 

Resources