Excel compare and replace between two spreadsheets

microsoft excelmicrosoft-excel-2010

I have a task of taking information from say spreadsheet A, i.e., two columns name and number

 Smith,joe | 3446

and comparing and entering the number field into the matching person on a separate spreadsheet.

Can anyone tell me a simple way in which to do this.

Best Answer

Assuming the name you want to search for and the name as it appears in the lookup table are identical, you'll want to use a VLOOKUP formula:

 =VLOOKUP([lookupname],[referencetable],[columnnumber],False)

As an example, if your name is in cell A2 and the reference table is on Sheet2 (with column A containing the name, column B containing the number, and the table spreading from row 1 to 900), your formula would look like this:

 =VLOOKUP(A2,Sheet2!$A$1:$B$900,2,False)

Note the dollar signs in the formula - these "lock" references, so you can copy the formula elsewhere without the reference moving about.

The "False" tells the formula to look only for exact matches.