HOW TO GET CELL ADDRESS BASED ON VALUE OF ANOTHER CELL?
I have a column in date format (A column), and it contains various rows. In E1 cell, I put a random date value, and below (E2) I need to get the cell address that date is equal the date above. I need the address like "A35" (without "").
I'm trying with "address" function, but no success. Can someone help?
CodePudding user response：
Answer
The following formula should produce the behaviour you want:
=ADDRESS(MATCH(E1,A:A,0),1,4)
Explanation
=MATCH searches a specified range for a specified value, and returns the index of that value (it's row number). In this case, the value to search for is in E1, and the range to search through is A:A.
=ADDRESS determines the A1 notation of a specific row and column. In this case, the row is determined by =MATCH and the column is column 1 (or column A). The 4 tells =ADDRESS to return the relative cell reference rather than the absolute cell reference (A35 instead of $A$35).
Functions used: