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.


--
Paul134
------------------------------------------------------------------------
Paul134's Profile: http://www.excelforum.com/member.php...o&userid=31735
View this thread: http://www.excelforum.com/showthread...hreadid=516275

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

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)),SUMPROD UCT((A1:P20=Q1)*COLUMN(A:P)))

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

Does that help?

Regards,
Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=516275

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

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

Paul.


--
Paul134
------------------------------------------------------------------------
Paul134's Profile: http://www.excelforum.com/member.php...o&userid=31735
View this thread: http://www.excelforum.com/showthread...hreadid=516275