How do I return an address from a range of cells in Excel, based on dynamically changing data?

Question

I am trying to return a specific cell address from a range of cells, where the cell address I need contains specific data.

So in my range (A1:O15) my user types in a number. I check to see if that number appears in the range, and want the cell address returned. So far I have this:

=IF(COUNTIF(A1:O15,Q19)>0,SUBSTITUTE(ADDRESS(ROW(),COLUMN()),"$",""),"")

Q19 is the cell which contains the text I want to search for in the range. This returns the value of the cell I have the formula typed into. I tried another way in which I added in the range to the Row and Column functions. In this case I get the first cell in the range, whether or not the data is there:

=INDIRECT(IF(A1:O15=Q19,ADDRESS(ROW(1:15),COLUMN(A:O)),""))

Ideally I would be able to repeat this formula so that it can recognise the numbers 1-30 in the range of cells, and tell me which cell each number is in.

How do I get the exact cell address returned?

Answer 1

Use sumproduct() with address() function to find address of cell based on value. Suppose you want to find address of value 5 then use below formula.

=ADDRESS(SUMPRODUCT(ROW($A$1:$C$3)*($A$1:$C$3=5)),SUMPRODUCT(COLUMN($A$1:$C$3)*($A$1:$C$3=5)))

If you want to make it dynamic then use cell instead of value then use-

=ADDRESS(SUMPRODUCT(ROW($A$1:$C$3)*($A$1:$C$3=C10)),SUMPRODUCT(COLUMN($A$1:$C$3)*($A$1:$C$3=C10)))

Answer 2

=IFERROR(ADDRESS(MAX((myRng=Number)*ROW(myRng)),MAX((myRng=Number)*COLUMN(myRng))),"Number not found in Range")

Substitute whatever cell references you want for the named ranges I used. You can also use optional ADDRESS function arguments depending on the format desired for the returned address.