Hi,
I have a name list range called Workdays in Sheet2 consists of name range
values A1~A5 = Monday ~ Friday.
In Sheet1, colum A, I have a data validation referring to this list.
If in cell A1, Friday is selected. I want to show "A5" in B2 of Sheet1. Can
anyone help?
Thanks so much for your help.

try this in B2 and your lookup value in B1

=ADDRESS(MATCH(B1,A1:A5,0),1,4)

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis










"YY san." wrote:

Hi,
I have a name list range called Workdays in Sheet2 consists of name range
values A1~A5 = Monday ~ Friday.
In Sheet1, colum A, I have a data validation referring to this list.
If in cell A1, Friday is selected. I want to show "A5" in B2 of Sheet1. Can
anyone help?
Thanks so much for your help.

There are several ways to do this. This one works no matter where your named
range Workdays is located.

Array entered** :

=ADDRESS(MAX((Workdays=A1)*ROW(Workdays)),COLUMN(W orkdays),4)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"YY san." wrote in message
...
Hi,
I have a name list range called Workdays in Sheet2 consists of name range
values A1~A5 = Monday ~ Friday.
In Sheet1, colum A, I have a data validation referring to this list.
If in cell A1, Friday is selected. I want to show "A5" in B2 of Sheet1.
Can
anyone help?
Thanks so much for your help.

P.S.

In other words, I'm assuming you want the *specific* cell address that
matches the selection from the drop down list.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
There are several ways to do this. This one works no matter where your
named range Workdays is located.

Array entered** :

=ADDRESS(MAX((Workdays=A1)*ROW(Workdays)),COLUMN(W orkdays),4)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT key then hit ENTER.

--
Biff
Microsoft Excel MVP


"YY san." wrote in message
...
Hi,
I have a name list range called Workdays in Sheet2 consists of name range
values A1~A5 = Monday ~ Friday.
In Sheet1, colum A, I have a data validation referring to this list.
If in cell A1, Friday is selected. I want to show "A5" in B2 of Sheet1.
Can
anyone help?
Thanks so much for your help.