How to create a drop down list from an array?

Question

I think this is a fairly simple thing to do (in theory...) but I can't seem to find the correct combination of lookup commands.

I have a data array on Worksheet 1 with a list of values (A, B, C, D, E, F).

On another worksheet, I want to have a lookup field that will search the array on Worksheet 1, match it for multiple variables (let's say, match B and E) and return AS A DROP DOWN LIST to choose from all the rows in the array on Worksheet 1 that match those two variables. (Like, last name and company name -- so I would enter "Smith", or set the field's match value to "Smith", and the drop down list would show me "John Smith, Acme Corporation" and "Samantha Smith, Integrated Technologies". I would then choose which of these people I wanted to enter into the row on Worksheet 2. Let's say it was Samantha's contact info I was after -- the match value or my entry would show me the two Smiths, I would click on Samantha, and the second worksheet would return Samatha's info.

I think I could probably do this more smoothly with a simple Access Database, but unfortunately I don't have ...uh...access to Access right at the moment so I'm hoping Excel can be massaged to make this work.

Thanks for any help!

-- Twin Owl

Answer 1

You are better off checking in the programming section of the forum.

You can still install and use  trial copy of Access 2013 part of Office 365 trial.