Tom’s Tutorials For Excel: LOOKUP and ADDRESS of the Last Instance

Tom’s Tutorials For Excel: LOOKUP and ADDRESS of the Last Instance

Sometimes you have a list of items that are repeated many times. You might only want information about the last entry of a particular item, such as its cell address or some lookup information in its adjacent cell.

In the following pictures, you see how the “Hats” item is being queried. In the first picture, this formula returns the most recent (that is, the bottom-most) inventory count for Hats:
=INDEX(A1:A20,MAX(INDEX((B1:B20=E1)*ROW(B1:B20),0)))

In the second picture, this formula returns the cell address of the last entry of Hats:
=ADDRESS(MAX(INDEX((B1:B20=E1)*ROW(B1:B20),0)),2)