return cell reference in a table based upon given lookup criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Travis - 13 Mar 2006 21:09 GMT

Is there a way to return the cell reference, or column/row coordinates,
of a cell within an array or table by providing lookup criteria?

Reply to this Message

Ron Coderre - 13 Mar 2006 21:36 GMT

Perhaps something like this:

For a table of value in A1:E10

F1: (the value to find)
G1: =ADDRESS(MAX((A1:E10=F1)*ROW(A1:E10)),MAX((A1:E10=F1)*COLUMN(A1:E10)))
Note: Commit that array formula by holding down the [Ctrl][Shift] keys and
press [Enter].

That formula returns the address of the 1st cell containing the value in F1,
or #VALUE! if there is no match.

Am I on the right track here?

***********
Regards,
Ron

XL2002, WinXP-Pro

> Is there a way to return the cell reference, or column/row coordinates,
> of a cell within an array or table by providing lookup criteria?

Reply to this Message

Herbert Seidenberg - 15 Mar 2006 05:24 GMT

If the value criteria occurs only once in array1,
=ADDRESS(SUMPRODUCT((array1=criteria)*ROW(array1)),
SUMPRODUCT((array1=criteria)*COLUMN(array1)),1,1)
otherwise post example.

Reply to this Message

Travis - 15 Mar 2006 15:31 GMT

Hello Herbert,

That worked perfect! Thanks a million!

Travis

Reply to this Message