Finding duplicates and copying relevant info.

Microsoft Excel mac 2008
March 18, 2010 at 10:31:21
Specs: Macintosh
Hello,

I have an Excel workbook with two spreadsheets.

On my first sheet I have two columns. 1st column is
blank, the second column contains names.

On my second sheet I have two columns. 1st column
contains information, the second column contains
many of the same names that are in column 2 of the
first sheet.

I am looking for a formula to enter into the 1st column
of the first sheet that will look at the second column of
the 1st sheet, compare it to the 2nd column of the
second sheet and if there is a match, copy the
information from the 1st column of the second sheet
to the 1st column of the first sheet.

Can anyone please help me?
Thank you in advance.
Daniel


See More: Finding duplicates and copying relevant info.

Report •


#1
March 18, 2010 at 11:15:34
re: On my first sheet I have two columns. 1st column is
blank, the second column contains names.

I'll assume this is Sheet1!A1:B8. A1:A8 is blank, B1:B8 contain your names.

On my second sheet I have two columns. 1st column
contains information, the second column contains
many of the same names that are in column 2 of the
first sheet.

I'll assume this is Sheet2!A1:B8. A1:A8 contain your "information", B1:B8 contain your names.


Try this in Sheet1!A1 and drag it down. This is sometimes known as doing a "Left Lookup".

=INDEX(Sheet2!$A$1:$A$8,MATCH(Sheet1!B1,Sheet2!$B$1:$B$8,0),1)

The MATCH formula will lookup whatever is in Sheet1!B1 in Sheet2!B1:B8 and return the position number of that value within the list.

The INDEX() function will use that value as the position number within Sheet2!A1:A8 and return the value in that cell. (The "1" at the end is the col_num for the INDEX function.)

If you need a more detailed explanation, let me know.


Report •

#2
March 18, 2010 at 11:30:16
I think I understand this formula, please tell me if I am
wrong.

I put your formula into Sheet1!A1
=INDEX(Sheet2!$A$1:$A$8,
This tells the cell to index the 2nd sheet, and the range is
A1-A8.

MATCH(Sheet1!B1,Sheet2!$B$1:$B$8,0),1)
This checks to see if there is a match between column2 of
sheet1 with column2 of sheet2.
This is where I get lost.


I hope I understood most of that correctly.


Report •

#3
March 18, 2010 at 11:35:54
I have tried this formula and I get #N/A as a result.

Report •

Related Solutions

#4
March 18, 2010 at 12:23:49
No, you're missing a little bit...

We need to look at the formula from the inside out since we are using MATCH to supply an argument for INDEX.

MATCH(Sheet1!B1,Sheet2!$B$1:$B$8,0)

MATCH(lookup_value,lookup_array,match_type)

The look_value is whatever is Sheet1!B1

The lookup_array is Sheet2!$B$1:$B$8

The match_type is 0 meaning we want an exact match.

So, we are asking Excel to find an exact match for Sheet1!B1 in Sheet2!B1:B8. If it finds it, it will return the position within the lookup_array. (In this case, the position will be the same as the Excel Row, but that's only because we started the lookup_array in Row 1, so position 1 is the same as Row 1)

OK, so let's say the MATCH function returns 2. That means it found a match for Sheet1!B1 in the 2nd position of Sheet2!B1:B8.

Now we'll look at the INDEX function:

INDEX(array,row_num,column_num)

INDEX will return a value from an array, based on the row_num and the column_num arguments supplied.

The array we used is Sheet2!A1:A8.

The row_num is the value returned by the MATCH function (2) and the column_num is the 1 at the end of the formula. Since there is only 1 column in A1:A8, we should get the value in A2. Again, the row_num used in the INDEX function is a position within the array, not an Excel Row number.

Does that help?


Report •

#5
March 18, 2010 at 12:55:58
That was a great explanation. Everything worked perfectly.
Thank you so much.

Report •

#6
March 18, 2010 at 14:09:27
Glad to have been of assitance.

Report •


Ask Question