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

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

Thanks. Paul.

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

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

Paul.