Highlighted
New Contributor

# Finding identical values in a different spreadsheet and copying over correlating values.

I am not intimately familiar with Excel, nor do I use it very often, so I apologize if my question is poorly phrased.

I have two spreadsheets. Spreadsheet 1 has a full list of numerical codes in one column and a set of corresponding numbers in a different column. Spreadsheet 2 has a column containing a subset of the codes from Spreadsheet 1, though they are in a different order. Spreadsheet 2 also has a column that needs to be filled with the corresponding numbers from Spreadsheet 1. A method of doing so automatically would be greatly appreciated.

Spreadsheet 1                +                Spreadsheet 2         --->Function--->       Spreadsheet 2

1000      1                                          1005        x                                                 1005     2

1001      1                                          1004        x                                                 1004     1

1002      2                                          1001        x                                                 1001     1

1003      0

1004      1

1005      2

Thanks.

3 Replies
Highlighted

# Re: Finding identical values in a different spreadsheet and copying over correlating values.

That could be like

`=IFERROR(INDEX(Sheet1!\$C:\$C,MATCH(Sheet2!\$B2,Sheet1!\$B:\$B,0)),"")`

Similar if they are different workbooks, not sheets.

Highlighted

# Re: Finding identical values in a different spreadsheet and copying over correlating values.

Thank you for the response. Seeing the function has shown me just how little I know about Excel. Which portions of the function would be changed to match a specific spreadsheet? I'd imagine Sheet1 and Sheet2 are changed to the proper names, but what about \$C or \$B2?

Highlighted

# Re: Finding identical values in a different spreadsheet and copying over correlating values.

B and C are columns in Sheet1 with your data, you may change any other ones, e.g. \$A:\$A instead of \$B:\$B

\$B2 is the first cell in Sheet2 with your code

Formula against it in C2. If you drag cell C2 down, formula in C3 will be with \$B3. Please check about absolute and relative references in Excel.