simple formula?

J

jrose

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
 
B

Bob Phillips

=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)
 
J

jrose

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.
 
B

Bob Phillips

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)
 
J

jrose

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?
 
B

Bob Phillips

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)
 
J

jrose

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?
 
Top