Tom’s Tutorials For Excel: Find Your Address

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)))


Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
2 comments on “Tom’s Tutorials For Excel: Find Your Address
  1. tracy says:

    In Firefox, your 2nd formula is cut off by the red box on the right. It wraps in IE

Leave a Reply

Your email address will not be published. Required fields are marked *

*