Find & copy value2 based on value1, 2 sheets

Microsoft office / 2010
January 2, 2011 at 10:37:43
Specs: Windows 7
I have two spreadsheets in Excel. One has a list of names and a blank column next to it for their respective jobs:

Lastname Job (blank)

The second spreadsheet has everyone's name (but with a title in front Ex. "OT Lastname") and then a column with everyone's job:

OT Lastname Station 1

Is there a function that will search the completed second worksheet, find the last name and job, and copy that respective job to the respective last name on the first, empty spreadsheet?

Does that make sense? Thank you for your help!


See More: Find & copy value2 based on value1, 2 sheets

Report •


#1
January 2, 2011 at 11:57:44
Without thinking about this too much, the first thing comes to mind is a hidden column to the left of the "OT Lastname" column to extract the last name and then a VLOOKUP to pull the "job".

Let's say you have this in Sheet1:

          A
1      Smith
2      Jones
3      Doe

In Sheet2 you have this:

Without thinking about this too much, the first thing comes to mind is a hidden column to the left of the "OT Lastname" column to extract the last name and then a VLOOKUP to pull the "job".

Let's say you have this in Sheet1:

          A             B
1      Dr Jones     Station 1
2      OT Doe       Station 2
3      Mr. Smith    Station 3

Insert a new Column A in Sheet2 and drag this formula down with this formula:

=MID(B1,FIND(" ",B1)+1,LEN(B1))

You should now have this:

             A         B            C
1         Jones     Dr Jones     Station 1
2         Doe       OT Doe       Station 2
3         Smith     Mr. Smith    Station 3

You can hide Column A if you'd like.

Now, back in the Sheet1, use this in Column B to pull the "job" from the table in Sheet2:

=VLOOKUP(A1,Sheet2!$A$1:$C$3,3,0)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question