What Excel formula would help find the address of a value in a range without headings?

Solution 1:

Try this:

enter image description here

Formulas from cells O3:O6

{=SUM((D2:L14=O2)*1)}
{=SUM(ROW(D2:L14)*(D2:L14=O2))}
{=SUM(COLUMN(D2:L14)*(D2:L14=O2))}
=IF(O3=1,ADDRESS(O4,O5,1),"Multiple occurences")

Formulas in {} are array formulas.

There is all-in-one formula in O7 cell:

{=IF(SUM((D2:L14=O2)*1)=1,ADDRESS(SUM(ROW(D2:L14)*(D2:L14=O2)),SUM(COLUMN(D2:L14)*(D2:L14=O2)),1),"Multiple occurences")}

Not sure why you would want the address, but you can try (with your lookup string in H1, and myRange refers to the range to search):

=ADDRESS(
      AGGREGATE(14,6,FIND(H1,myRange)*ROW(myRange),1),
      AGGREGATE(14,6,FIND(H1,myRange)*COLUMN(myRange),1))

enter image description here

And it doesn't matter if there are blank rows/columns in myRange.