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

by Tom Urtis 3 Comments ↓

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.

‹ Tom’s Tutorials For Excel: Dynamic Summing From the Active Cell

Tom’s Tutorials For Excel: Entering a Function’s Arguments Tooltip in a Cell ›

Posted in Tom's Tutorials for Excel
Tags: Analytics, Array Formulas, Big Data, Business Intelligence, Cells Ranges Lists, Data Science, Excel Expert, Excel Guru, Formulas Functions, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Tom Urtis

3 comments on “Tom’s Tutorials For Excel: Looking Up the Address of an Item in a List”

1. Alex says:

   March 6, 2017 at 11:09 pm

   Hello,

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

   Greetings from Austria, Alex

   Reply
   • Tom Urtis says:

     March 6, 2017 at 11:36 pm

     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.

     Reply
2. Hussein Korish says:

   March 4, 2018 at 3:08 pm

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

   Reply