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
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)

     

Resources