Hi Ruth,

It sounds like you are looking for a Vlookup type scenario...

http://www.contextures.com/xlFunctions02.html

If you select Smith in C5 you want the address for Smith to automatically show up in D5?

If so, create the lookup table and then put the Vlookup in D5 and copy down as far as required.