linking to other worksheets and updating a file

[Bodster]

This is what I want to do,

Sheet 1 contains a list of surnames in column 1, forenames in column 2 along
with other data in columns 3, 4, .......

Sheet 2 contains a list of suranmes, forenames some of which are the same as
in sheet 1.

I want to add a column in sheet 1 which takes the name of the person from
sheet 1, looks up that name in sheet 2 and chooses data from one of the
columns in sheet 2 and places that data back into the correct row and new
column in sheet 1.

A further complication might be that there are duplicate surnames in the
first sheet so need to look up the forenames in these circumstances.

Looking forward to any help,

Cheers

Bodster

 

[Earl Kiosterud]

Bodster,

You can use VLOOKUP. It looks down one column, so in sheet 2 you'll need to make an
additional column with the surname and forename concatenated. In this case, it could go in
column A, which you will have inserted, causing all the columns to move over. It could also
go in column C (also inserted). In any case, it must be left of the columns which the
VLOOKUPs in sheet will be retrieving data.

= B2 & " " & C2

Now in Sheet 1, in whatever column you want extracted data from sheet 2 to appear, put

=VLOOKUP(A2 & " " & B2, Sheet2!A2:E21,4,FALSE)

This is for row 2 of sheet 1, and retrieves the associated data from column 4 of sheet 2.
Change as necessary.

--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...