Forum Discussion

tcaseria's avatar
tcaseria
Copper Contributor
Aug 04, 2022

Find Cell Reference of a Value Within a Table

I'm looking to input a value in a cell and have a formula find the cell reference of that value within a table. Here is a screenshot to show what I mean. 

 

excel
Formulas and Functions

6 Replies

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Aug 04, 2022

    tcaseria 

    Perhaps this (Ctrl_Shift_Enter):

    =ADDRESS(SMALL(IF(range=K2,ROW(range)),1),SMALL(IF(range=K2,COLUMN(range)),1),4)

     

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Aug 04, 2022

    tcaseria 

    If you don't have the very latest version of Excel:

     

    =ADDRESS(MIN(IF(A1:H15=K2,ROW(A1:H15))),MIN(IF(A1:H15=K2,COLUMN(A1:H15))))

    • tcaseria's avatar
      tcaseria
      Copper Contributor
      Aug 04, 2022
      Would there be a way to do this if the values were strings instead of numbers?
      • Harun24HR's avatar
        Harun24HR
        Bronze Contributor
        Aug 05, 2022

        tcaseria 

        Below formula should work both for numbers and texts. Try-

        =ADDRESS(MAX(ROW(A1:H15)*(A1:H15=K2)),MAX(COLUMN(A1:H15)*(A1:H15=K2)),4)

         

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Aug 04, 2022

    tcaseria 

     

    Try this (Range being your data range or named item).

    =REDUCE("",range,LAMBDA(a,v,IF(v=K2,ADDRESS(ROW(v),COLUMN(v),4),a)))

     

Resources