Having a Cell Display text related to a text value in another

[Lazy_T]

Hi Guys,

Apologies for the horrendously worded title - I have list of about 40 names, and i'd like to use data validation to create multiple cells with a dropdown list with the ability to choose from all of the names. I'd then like the adjacent cell to automatically display the email address for that person next to it.

How do I do this, please?

Given the amount of info, an IF function isn't powerful enough I don think? (I could be wrong here).

What are my options, if so? Something in VBA?

e.g.

Lazy_T	lazy_t@lazyt.com
XXXXX	......

<tbody>
</tbody>

Appreciate this is a big ask, but I am struggling!

 

[JazzSP8]

Sounds like you need to look at VLOOKUP.

Create a table of all your names / email addresses, say in Sheet 2, Columns A and B.

Assuming your drop down box is in cell A1 you'd then use something like;

=VLOOKUP(A1,Sheet1!A:B,2,false)

The "2" is the column number from the original value, you're basically saying

"Look for this name, in this column and then pull in the value next to it"

Make sense?

 

[Lazy_T]

Sounds sensible to me; never been very good with VLOOKUP.

I will have a crack - and report back!

Thank you, much obliged!

 

[JazzSP8]

It's easy when you get the hang of it, probably better if I gave you a better start though...

=VLOOKUP(A1,Sheet[COLOR=#ff0000]2[/COLOR]!A:B,2,false)

Sorry about that!

 

[Lazy_T]

Thank you so much - this has worked perfectly - I've even managed to crudely adjust this account for phone numbers too.

I'm sure there it a better way than I have just used, but it works very, very well.

Thank you again!