Forum Discussion

Alecs's avatar
Alecs
Brass Contributor
Jun 08, 2022
Solved

List All Matched Cells that contain a given value

Hello guys, I'm looking for a formula that can match all values from a range and put the result in the correspondent column. This is an example. I've tried to make it as easy as possible.   Here i...
excel
Formulas and Functions
  • mtarler's avatar
    mtarler
    Jun 08, 2022

    Alecs This would be even better with the new text functions but here is a version using current Excel 365 functionality:

    =LET(in,J3:R9,rs,ROWS(in),cs,COLUMNS(in),s,SEQUENCE(rs*cs,1,0),col,INDEX(in,QUOTIENT(s,cs)+1,MOD(s,cs)+1),FILTER(col,ISNUMBER(SEARCH(P$12,col)),""))
mtarler's avatar
mtarler
Silver Contributor
Jun 08, 2022

Alecs This would be even better with the new text functions but here is a version using current Excel 365 functionality:

=LET(in,J3:R9,rs,ROWS(in),cs,COLUMNS(in),s,SEQUENCE(rs*cs,1,0),col,INDEX(in,QUOTIENT(s,cs)+1,MOD(s,cs)+1),FILTER(col,ISNUMBER(SEARCH(P$12,col)),""))
Alecs's avatar
Alecs
Brass Contributor
Jun 08, 2022
That's perfect! thanks a lot!

Resources