Copy data between excel spreadsheets with a given value as a parameter

Question

Say I have two spreadsheets.

One of the spreadsheets has a table like this:

  **Sheet 1**
  A        B        C        D
1 Name     Age      Rank     Number

2 George   22       Master   12

3 Paul     21       Acolyte  22

4 Jimmy    32       Horse    33
 
5 Agnor    12       Cheetah  90

The other spreadsheet looks like this:

  **Sheet 2**
  A        B        C        D
1 Name     Age      Rank     Number

2 George   

3 Paul     21                22

4 Agnor

I want to copy Ages from sheet 1 to sheet 2. So I would copy George, Paul, and Agnor's age, and ignore Jimmy. Which function should I use to copy data based on an existing value?

Is there something like:

foreach VAL in Sheet1
if(Sheet1.Name.EqualsIgnoreCase(Sheet2.Name))
{
    Sheet2.Age = Sheet1.Age;
}

I looked this up, and a lot of people are saying to use VLookup, but I can't figure out how that would apply.

EDIT

I found a similar resolved question (No idea HOW I missed it) here: Compare and copy data between worksheets

However, I'm still a little shaky on how to implement this (I'm using Excel 2007).

SeanC · Accepted Answer · 2012-05-29 21:44:07Z

1

vlookup would be the easiest way to go.
I will use cell B2 in sheet2 as the reference to try to explain the function
=VLOOKUP(A2,Sheet1!$A$2:$D$5,2,FALSE)
The first parameter is the data you are using for a lookup - you may want to change that to $A2 if you copy the formula around the sheet
The second parameter is where the data is that excel needs to find the value you referenced in the first parameter - use dollar signs. The data doesn't tend to move
The third parameter tells excel what info to return - the 2 means give me the 2nd piece of data
The last parameter tells excel if it can use a close match (FALSE for an exact match)

Now to follow Excel and see what it returns.....

First, it looks for George in the data (it knows it has to be an exact match from the fourth parameter)
Next, it finds the row George -- 22 -- Master -- 12 and gives the function the second value (22 in this case)

edited May 29, 2012 at 21:44

answered May 29, 2012 at 19:32

SeanC

16k5 gold badges48 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

gfppaste Over a year ago

So if I have say 5000 rows in sheet 1, and I only have 2000 matching parameters in sheet 2 (say I only have 2000 matching names, both in column A of their respective sheets)... will this function parse through the entire set of data in sheet 1? (As in, if I copy this to every cell in Sheet 2, row B, will this run a comparison for each value of sheet 2, row B against all values of sheet 1, row A, or is there additional logic I would need to add to this? Thank you for the explanation!

SeanC Over a year ago

yes. it will only search for the data you want - if the data is missing (e.g. searching for Ringo on your example data) excel will return #N/A. use iferror around the function if that's a concern

Collectives™ on Stack Overflow

Copy data between excel spreadsheets with a given value as a parameter

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related