Microsoft Excel Experts...

CyclingBantam

... I'm hoping one of you clever people will be able to help me out.

I am trying to put a formula in excel which will mean, when in cell A1 a name is picked from a drop down list , cell B1 gets populated with appliccable data (Say, an address (which I know and I will input to excel wherever needed).

Yet for the life of me I can't seem to do it. It must be easy. Does anyone know?

Thanks,

Ben

MattC59

I went on an excel training course a month or so back, I seem to remember we covered it. I'll look through the exercises for you, but I'm pretty sure that someone will beat me to it

jawooga

Think this is the right sort of thing:

Make a table somewhere in you worksheet e.g.

___AA______ AB__
1 Person1 Address1
2 Person2 Address2

Make drop down list:
Select A1. Click data from top tool bar, then validation. From Settings, Allow 'list'. Source, select the range within column AA to include all your available names.

Populate B1:
In B1, type "=vlookup(A1, AA1:AB2, 2, FALSE)"

[
In this case AA1:AB2 defines the range where both your Person and Address data sits.
A1 is the field your looking up (as specified from drop down list)
2 (third field) means look in your table AA1:AB2 for the value in A1 and return the second column which corresponds. If your lookup table had a third column with telephone number for example you could use 3 to return this in to B1.
FALSE specifies exact match.
]
hth

Ands

You could use a lookup table and a vlookup/hlookup function (lookup value x and return value y). Or if it is a small selection of options use an If statement or nested If statements.

CyclingBantam

Thank you all very much! I knew you would all know!

morstar

Use Index and Match. So much more spangly.

Sorry, I am just being a smart arse as the question has already been answered and I'm a bit of an Excel nerd.

maddog 2

+1

Match and Index are more flexible than the lookups

desweller

Or write a bit of VBA to do it. Much more predictable IMO.

Too nerdy?