Formula to match and extract between two spreadsheets

**Jhubbard** · 05-07-2013, 03:06 PM

Both spread sheets have a list of names in column A and Salaries in column E (I could move them so they are side by side) I want 2nd spreadsheet to Match the names from it's own column A to that of the first sheets column A and if they match extract the Salary from the same row. I am basically trying to just compare salaries for each person from last year to this year. There will be some new people who wont match. Any suggestions?

**Roseman** · 05-07-2013, 04:56 PM

In column F insert this formula

Formula:

=vlookup(a1,'[spreadsheetA.xls]sheet1'!$a$1:$e$100,5)

and copy it down the whole column.
Where in the second argument you should put in the whole range of the data in the first worksheet, and the 3rd argument should be the column number in which the salary is.

**Jhubbard** · 05-08-2013, 11:40 AM

Fun-tastic! I love excel! that worked, this is what the formula ended up looking like in excel 2013 =VLOOKUP(A2,'9282012'!A:B,2,FALSE) For anyone who is looking at this with the same question in mind here is the full explanation;
A2 was the first name in the A column of the worksheet the formula is in, as I double clicked the right lower corner of the formula cell it auto filled down the formula's column, so A2 then becomes A3, 4, 5 etc.
'9282012'!A:B is the second spreadsheet name (9282012) and columns A (names to match) & B (salary to extract if the names match) This will auto enter into the formula when you click the tab of the other sheet and then select the array containing the info.
2 is telling the formula I want to extract the info from B, the second column if there is a match
FALSE is telling the formula I only want the information to be extracted from B if it is an exact match.