A function that looks for a value on a sheet on unknown column or row

P

Paul134

How can I make a function that looks for a value on a sheet, on unknow
column or row, and returns row and column position where it found it.
presume it has to be done using MATCH in an array formula that searche
entire columns from 1 to 255 and ISNA to ignore the unmatched columns
and ADDRESS to inform the cell found... I've been trying unsuscesfull
to assemble it and make it work...

Thanks. Paul
 
R

Ron Coderre

Try playing with this:

I put the phrase "Find Me" in a cell in the range A1:p20.


Q1: Find Me
Q2:
=ADDRESS(SUMPRODUCT((A1:p20=Q1)*ROW(1:20)),SUMPRODUCT((A1:p20=Q1)*COLUMN(A:p)))

With the phrase in C18, the formula returned: $C$18

Does that help?

Regards,
Ron
 
P

Paul134

Thank you Ron, array formulas seem sooo useful... I hope one day I'll be
able to create one that just simply works...

Paul.
 
Top