You can use a formula that returns the cell address of a particular item in a worksheet. In the example below, cell D2 contains a Cog or Sprocket Stock Number, and cell E2 contains the following 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 brackets, { and }, seen in the formula entered in D5 below are not entered by the user. Excel applies these to show the formula has been input as an array formula.

Example

  A B C D E
1 BEST Cogs & Sprockets   Stock Number Address
2 Stock Number Sales   GFAS321 $A$5
3 HFTS864 $ 3,199      
4 KGHT547 $ 6,530   Formula used in E2 Result
5 GFAS321 $ 7,789   {=ADDRESS(MIN(IF(A3:A6=D2,ROW(3:6))),1)} $A$5
6 RJND947 $ 8,930