Find Cell Reference of a Value Within a Table

Occasional Contributor

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. 

 

Screenshot 2022-08-04 133331.png

6 Replies

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

 

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

Would there be a way to do this if the values were strings instead of numbers?

@tcaseria 

Yes, it should work the same way.

@tcaseria 

Perhaps this (Ctrl_Shift_Enter):

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

 

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

Harun24HR_1-1659668476938.png