Return cell reference instead of value
 

I want to use a lookup-type function to look in cell A1 (Sheet 1) for
instance, and find a match in column A (Sheet 2); then, return the CELL
REFERENCE (for example, A216), instead of the value in cell B1 (Sheet
1). Does anyone know if this can be done?

EXAMPLE
Sheet 1
Column A Column B
Row 1 Monday A216

(Column B contains a formula resulting in a cell reference "A216",
rather than the value "Monday")

Sheet 2

Column A
Row 215 Sunday
Row 216 Monday
Row 217 Tuesday


--
lmason
------------------------------------------------------------------------
lmason's Profile: http://www.excelforum.com/member.php...o&userid=37921
View this thread: http://www.excelforum.com/showthread...hreadid=574460

Return cell reference instead of value
 

try this

=ADDRESS(MATCH(A1,Sheet2!A1:A100,0),1,4)

put in B1 of sheet1 and drag it down

hope it would serve your purpose.
lmason Wrote:
I want to use a lookup-type function to look in cell A1 (Sheet 1) for
instance, and find a match in column A (Sheet 2); then, return the CELL
REFERENCE (for example, A216), instead of the value in cell B1 (Sheet
1). Does anyone know if this can be done?

EXAMPLE
Sheet 1
Column A Column B
Row 1 Monday A216

(Column B contains a formula resulting in a cell reference "A216",
rather than the value "Monday")

Sheet 2

Column A
Row 215 Sunday
Row 216 Monday
Row 217 Tuesday


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=574460