Excel, filling in cell values automatically [Solved/Closed]

Report
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
Hello,

I have two worksheets in Excel 2007 and am trying to automatically fill in cells in worksheet two based on values in the first worksheet. Any assistance in code would be greatly appreciated!

Both worksheets contain name, lastname, address, phone of people (no unique identifiers though). Some of these people's details are repeated in worksheet2. Whenever an identical name (first name, last name) is typed into the second worksheet, I would like the contact details fields in worksheet 2(address, phone number, etc) to be filled automatically.

Could anyone help with the code?

Cheers

2 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
I give you small sample data. try this on sample data and you can modify

sheet 1 contains data from A1 to D5


first name last name address phone no.
a s sssss 123
a d ddddd 234
s d ffffffff 345
f g gggggg 678


sheet 2 contains data from A1 to B3


first name last name
a d
f g


in the second sheet in C2 copy this formula
=INDEX(Sheet1!C$2:C$5,MATCH(1,((Sheet1!$A$2:$A$5=A2)*(Sheet1!$B$2:$B$5=B2)),1))

INVOKE THIS FORMULA WITH CONTROL+SHIFT+ENTER

copy C2 down and to right.

you get the results like this


first name last name address phoneno.
a d ddddd 678
f g gggggg 678


Hope you can modify to suit you.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Hi, thanks for your help. Not sure what I'm doing wrong... I tried this but it came up with #N/A error, so I tried to use exactly the data and worksheets that you described in your example, but the code still didn't work for me. When I show the Calculation Steps to try and see what the prob is, it has:
=INDEX(Sheet1!C$2:C$5,MATCH(1, 0, 1))
please disregard my last comment!! I got it working - I had forgotten to use your instruction to CTL SHIFT ENTER to invoke formula.

Thank you so much!!! :)
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
you are welcome.