Tom’s Tutorials For Excel: Find Your Address
Suppose you have unique items in column C such as a list of the world’s major cities. If you want to find the cell address of a particular city, say London for example, this formula will return $C$4 as shown in the next picture.
=CELL("Address",INDEX(C:C,MATCH("London",C:C,0)))
But what if you want to find a unique item in a table that has many columns? You can still find the address of what you are looking for, as shown in the next example of a table having three columns of unique items. The same versatile formula exists in cell F2 for all 3 scenarios:
=ADDRESS(SUMPRODUCT((A1:C10000=E2)*ROW(A1:A10000)),
SUMPRODUCT((A1:C10000=E2)*COLUMN(A1:C1)))
In Firefox, your 2nd formula is cut off by the red box on the right. It wraps in IE
Hi Tracy, thank you for telling me that. I’ve now set the formula into two lines. Welcome to my Blog !!