To convert the text result of the
ADDRESS() function to an actual cell reference, you can use the
INDIRECT() function.
Here's an example formula that uses both functions to return the value in the cell referenced by the
ADDRESS() function:
Formula:
=INDIRECT(ADDRESS(MATCH(1,E3:E5,0)+2,3))
In this formula, the
ADDRESS() function returns the text representation of the cell reference, and the
MATCH() function is used to find the row number of the desired cell. The
+2 is added to the result of the
MATCH() function to account for the fact that the data starts in row 3 (not row 1).
The
INDIRECT() function then takes the text result of the
ADDRESS() function and converts it to an actual cell reference. This allows the formula to return the value in the desired cell (in this case, "Adams").
- Use the ADDRESS() function to return the text representation of the cell reference
- Use the MATCH() function to find the row number of the desired cell
- Add +2 to the result of the MATCH() function to account for the fact that the data starts in row 3 (not row 1)
- Use the INDIRECT() function to convert the text result of the ADDRESS() function to an actual cell reference