Find the reference's position within a range

[ExcAnd]

Hello, does anybody know, whether it's possible to find the position of a value within given range? The position of the value varies, it occurs only once though. For example, I want to find out in which cell is the value of the cell A1 within the range B2:Z30. I was trying to solve this via match function, however the column or row varies. And I prefer not to write a combination of match formulas for each column and row.

Many thanks in advance!

[JohnTopley]

One way ..

=ADDRESS(SUMPRODUCT(($B$2:$Z$30=$A$1)*ROW($B$2:$Z$30)),SUMPRODUCT(($B$2:$Z$30=$A$1)*COLUMN($B$2:$Z$30)))

I am sure there is a "smarter" way

[ExcAnd]

Thank you! It works, although I don't understand why
But it certainly does

[Tony Valko]

Here's another one...

Array entered**:

=ADDRESS(MAX(IF(B2:Z30=A1,ROW(B2:Z30))),MAX(IF(B2:Z30=A1,COLUMN(B2:Z30))),4)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.