return cell reference in a table based upon given lookup criteria

  • Thread starter Thread starter Travis
  • Start date Start date
T

Travis

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?
 
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
 
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.
 
Back
Top