Hello, Again i find myself in need of what should be an easy formula...but
unable to find what i need. I appreciate any help.
I have a small list of email addresses (AB18:AB22). I have a pull down menu
linked to cell AB17 with the peoples name in it. I simply want one cell to
display the email address of the person chosen in the pull down. This seems
too simple to even ask.... Thanks

=INDEX(AB18:AB22,MAX(IF(NOT(ISERROR(LEFT(AB18:AB22 ,FIND("@",AB18:AB220)-1)=A
B17)),ROW(AB18:AB22)-MIN(ROW(AB18:AB22))+1,0)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"jrose" wrote in message
...
Hello, Again i find myself in need of what should be an easy formula...but
unable to find what i need. I appreciate any help.
I have a small list of email addresses (AB18:AB22). I have a pull down
menu
linked to cell AB17 with the peoples name in it. I simply want one cell to
display the email address of the person chosen in the pull down. This
seems
too simple to even ask.... Thanks

This sounds like a job for VLOOKUP().


--
BruceP
------------------------------------------------------------------------
BruceP's Profile: http://www.excelforum.com/member.php...o&userid=33653
View this thread: http://www.excelforum.com/showthread...hreadid=534959

I appreciate the help cuz this is way over my head... however it does not
work. It returns just one of the email addresses on the list.

"Bob Phillips" wrote:

=INDEX(AB18:AB22,MAX(IF(NOT(ISERROR(LEFT(AB18:AB22 ,FIND("@",AB18:AB220)-1)=A
B17)),ROW(AB18:AB22)-MIN(ROW(AB18:AB22))+1,0)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"jrose" wrote in message
...
Hello, Again i find myself in need of what should be an easy formula...but
unable to find what i need. I appreciate any help.
I have a small list of email addresses (AB18:AB22). I have a pull down
menu
linked to cell AB17 with the peoples name in it. I simply want one cell to
display the email address of the person chosen in the pull down. This
seems
too simple to even ask.... Thanks

I thought that was what you wanted.

List your data and expected results, it always helps.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"jrose" wrote in message
...
I appreciate the help cuz this is way over my head... however it does not
work. It returns just one of the email addresses on the list.

"Bob Phillips" wrote:


=INDEX(AB18:AB22,MAX(IF(NOT(ISERROR(LEFT(AB18:AB22 ,FIND("@",AB18:AB220)-1)=A
B17)),ROW(AB18:AB22)-MIN(ROW(AB18:AB22))+1,0)))

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"jrose" wrote in message
...
Hello, Again i find myself in need of what should be an easy
formula...but
unable to find what i need. I appreciate any help.
I have a small list of email addresses (AB18:AB22). I have a pull down
menu
linked to cell AB17 with the peoples name in it. I simply want one
cell to
display the email address of the person chosen in the pull down. This
seems
too simple to even ask.... Thanks

ok. I did not make it clear. sorry. I have 5 email address..each in a cell
AB24:28. I have a pulldown with those peoples names that is linked to cell
AB17, returning a number 1-5 in that cell. I just want the email address
associated with the person that is selected in the pull down to be displayed
in a given cell. Does that make sense?

"Bob Phillips" wrote:

I thought that was what you wanted.

List your data and expected results, it always helps.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"jrose" wrote in message
...
I appreciate the help cuz this is way over my head... however it does not
work. It returns just one of the email addresses on the list.

"Bob Phillips" wrote:


=INDEX(AB18:AB22,MAX(IF(NOT(ISERROR(LEFT(AB18:AB22 ,FIND("@",AB18:AB220)-1)=A
B17)),ROW(AB18:AB22)-MIN(ROW(AB18:AB22))+1,0)))

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"jrose" wrote in message
...
Hello, Again i find myself in need of what should be an easy
formula...but
unable to find what i need. I appreciate any help.
I have a small list of email addresses (AB18:AB22). I have a pull down
menu
linked to cell AB17 with the peoples name in it. I simply want one
cell to
display the email address of the person chosen in the pull down. This
seems
too simple to even ask.... Thanks

Ah, you get the INDEX in the linked cell, I was thinking Data Validation
returning the value. So just use

=INDEX(AB18:AB22,AB17)

I have kept your original ranges not the latest ones <g

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"jrose" wrote in message
...
ok. I did not make it clear. sorry. I have 5 email address..each in a cell
AB24:28. I have a pulldown with those peoples names that is linked to cell
AB17, returning a number 1-5 in that cell. I just want the email address
associated with the person that is selected in the pull down to be
displayed
in a given cell. Does that make sense?

"Bob Phillips" wrote:

I thought that was what you wanted.

List your data and expected results, it always helps.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"jrose" wrote in message
...
I appreciate the help cuz this is way over my head... however it does
not
work. It returns just one of the email addresses on the list.

"Bob Phillips" wrote:



=INDEX(AB18:AB22,MAX(IF(NOT(ISERROR(LEFT(AB18:AB22 ,FIND("@",AB18:AB220)-1)=A
B17)),ROW(AB18:AB22)-MIN(ROW(AB18:AB22))+1,0)))

which is an array formula, it should be committed with
Ctrl-Shift-Enter,
not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"jrose" wrote in message
...
Hello, Again i find myself in need of what should be an easy
formula...but
unable to find what i need. I appreciate any help.
I have a small list of email addresses (AB18:AB22). I have a pull
down
menu
linked to cell AB17 with the peoples name in it. I simply want one
cell to
display the email address of the person chosen in the pull down.
This
seems
too simple to even ask.... Thanks

Thanks so much. That was a much easier formula. How would i make the cell
where this formula is a clickable link. The email address is now returned
into this given cell but it is not a clickable link to the address. Is that
possible?

"Bob Phillips" wrote:

Ah, you get the INDEX in the linked cell, I was thinking Data Validation
returning the value. So just use

=INDEX(AB18:AB22,AB17)

I have kept your original ranges not the latest ones <g

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"jrose" wrote in message
...
ok. I did not make it clear. sorry. I have 5 email address..each in a cell
AB24:28. I have a pulldown with those peoples names that is linked to cell
AB17, returning a number 1-5 in that cell. I just want the email address
associated with the person that is selected in the pull down to be
displayed
in a given cell. Does that make sense?

"Bob Phillips" wrote:

I thought that was what you wanted.

List your data and expected results, it always helps.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"jrose" wrote in message
...
I appreciate the help cuz this is way over my head... however it does
not
work. It returns just one of the email addresses on the list.

"Bob Phillips" wrote:



=INDEX(AB18:AB22,MAX(IF(NOT(ISERROR(LEFT(AB18:AB22 ,FIND("@",AB18:AB220)-1)=A
B17)),ROW(AB18:AB22)-MIN(ROW(AB18:AB22))+1,0)))

which is an array formula, it should be committed with
Ctrl-Shift-Enter,
not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"jrose" wrote in message
...
Hello, Again i find myself in need of what should be an easy
formula...but
unable to find what i need. I appreciate any help.
I have a small list of email addresses (AB18:AB22). I have a pull
down
menu
linked to cell AB17 with the peoples name in it. I simply want one
cell to
display the email address of the person chosen in the pull down.
This
seems
too simple to even ask.... Thanks