formula or shortcut for auto populating cells

[1overpar]

i'm trying to find a forumula that automatically populates 2+more cells with corresponding data. For example if i entered "abc" into a cell "john doe" and "25 maple drive" would both automatically populate.

I know how to do it manually but I was wondering if there was a shortcut with an array.

Thank you!

-Tony

 

[jimbojones]

Hi,

Not very clear of your exact requirements. If you have a list in say C1:C10 showing possible entries for A1 (i.e. abc in your example) then in D1:D10 you have names (john doe next to abc in your sample) then in E1:E10 you have the address (25 maple drive in your sample). Then in A2 put:

=Vlookup($A$1,$C$1:$E$10,2,false) then in A3:
=Vlookup($A$1,$C$1:$E$10,3,false)

would be one way.

Any way you do it you must have the mappings of code to name and address held somewhere!

James

 

[1overpar]

Thanks for the reply. However I was insufficient in my explanantion.

If say A1 is where I want to enter "abc", then B1 is where "John Doe" would auto populate and C1 is where "25 maple drive" would auto populate. I've created what I think are mappings. G:G would include all of the arbitrary "abc" entries, h:h all the names and i:i the addresses.

I tried the vlookup but wasn't successful. I'm thinking it's because i didn't specify that the name and address were to populate in different cells.

Many thanks for your help!

-Tony

 

[jimbojones]

Hi,

VLOOKUP should work. Based on your last post cell B1 formula would be:

=VLOOKUP(A1,G1:I65536,2,false)

That will return column 2 of the relative range G:I (i.e. column H).

What errors are you getting?

James

 

[1overpar]

James,

Thanks a million! It works perfectly! It's very cool. I think I'm just excelliliterate.

Thanks, thanks, thanks! Have a great weekend!

-Tony

 

[jimbojones]

Ha.

Good. Glad to hear that you got it working. VLOOKUP is very useful formula. That and SUMIF make life very easy for me.

Thanks. Have a good weekend yourself.

James