Help with functions that displays address and cell contents in Excel 2010

We are trying to achieve to correctly display the "Real" cell address of a inquired cell.  Then use that value to display the cells actual contents.  Unfortunate, we have not been successful.

To best illustrate, in the attached excel you will find that "joe" is located at cell 'B4' but the "Address" function used display '$A$4'.  Also the function to display the contents cell in 'E6' is not working correctly.


Please advice on the best way to accomplish this?
CellContents.xlsx
rayluvsAsked:
Who is Participating?
 
Wayne Taylor (webtubbs)Connect With a Mentor Commented:
The reason it returns A4 is because the MATCH function returns the position in the array, in this case 4, and you are specifying the 1st column ("A") in the second argument of the ADDRESS function. Use 2 instead and it will correctly return B4...

=ADDRESS(MATCH(E2,B:B,0),2)

Alternatively, use the CELL function with a combination of INDEX/MATCH to get the cell reference...

=CELL("address", INDEX(B:B, MATCH(E2,B:B,0)))
0
 
AlanConnect With a Mentor ConsultantCommented:
Hi

Change your formula to:

=ADDRESS(MATCH(E2,B:B,0),2)

The last number denotes the column - you were pointing to Column A.

Alan.
0
 
Wayne Taylor (webtubbs)Connect With a Mentor Commented:
To then use that returned address to return the cells contents, use the INDIRECT function...

=INDIRECT(E4)
0
 
rayluvsAuthor Commented:
Thank you very much!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.