Click here for important information about Computing.net.

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

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_numfor the INDEX function.)If you need a more detailed explanation, let me know.

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.

I have tried this formula and I get #N/A as a result.

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

positionwithin 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, soposition1 is the same asRow1)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

positionwithin the array, not an Excel Row number.Does that help?

That was a great explanation. Everything worked perfectly.

Thank you so much.

Glad to have been of assitance.

Ask Your Question

Weekly Poll

Do you think Intel's new hybrid chips will better compete with Apple/ARM?

Discuss in The Lounge

Poll History