Tom’s Tutorials For Excel: Looking Up the Address of an Item in a List

Tom’s Tutorials For Excel: Looking Up the Address of an Item in a List

Here is a formula that returns the address of the cell in a list that contains a particular item. In the picture, cell D2 contains a Widget Stock Number, and cell E2 contains this array formula to return the item’s address:
=ADDRESS(MIN(IF(A3:A15=D2,ROW(3:15))),1)

Recall, an array formula is applied to a cell by simultaneously pressing the Ctrl+Shift+Enter keys, not just Enter. The curly braces are not typed in by you; Excel places them automatically when the array formula is properly applied.

If you are unfamiliar with array formulas, see my video and explanation of arrays here.

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,
3 comments on “Tom’s Tutorials For Excel: Looking Up the Address of an Item in a List
  1. Alex says:

    Hello,

    why does your formula work with commas? I have to use semicolons. I use Excel 2010, German Edition.

    Greetings from Austria, Alex

    • Tom Urtis says:

      It might be a difference due to country or region. I’m in the United States and I just tried the formula using semicolons instead of commas and it failed. I’ve seen a few stories on the web about this.

  2. Hussein Korish says:

    The difference is because the Region Format in windows control panel ,you can choose the way how to seperate numbers.

Leave a Reply

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

*