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.

Originally Posted by lmason

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

Good afternoon,

What about if column 2 has multiple rows with "Monday" and you want to know the address range for those values? The list is sorted alphabetically, so all the cells with "Monday"
would be in sequence and not out of order.

Thanks.

The thread you are responding to was from 15 years ago - I doubt if the contributors are still monitoring the thread.

Besides, if you read the Forum Rules (available on the menu at the top of the screen), you would see that Rule 04 says you should not try to hijack someone else's thread by asking your own question - start a new thread instead.

Pete