Excel – Get the cell address using excel cell formula

excelexcel-formulavba

My date range starting point can vary at any column, but the row is always fixed at row 2.
For example, my current date range covers from A2:AB2
col A | col B | col C | …
blank | 1-Jan | 2-Jan | …

I need to achieve the following:
If I enter a date(eg. 2-Jan) at cell A5, cell B5 will display the cell address ($C$2) which contains the date (2-Jan).

How do I achieve this?

Best Answer

The following formula in cell B5 should do the job:

  =IFERROR(ADDRESS(2,MATCH(A5,$A2:$AB2,0)),"")

Note that row 2 is hard-coded in the formula by the 2 in the address function.

Related Topic