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

Question

I am not intimately familiar with Excel, nor do I use it very often, so I apologize if my question is poorly phrased.&nbsp;&nbsp;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.&nbsp;Spreadsheet 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; +&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Spreadsheet 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;---&gt;Function---&gt;&nbsp; &nbsp; &nbsp; &nbsp;Spreadsheet 21000&nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1005&nbsp; &nbsp; &nbsp; &nbsp; x&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1005&nbsp; &nbsp; &nbsp;21001&nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1004&nbsp; &nbsp; &nbsp; &nbsp; x&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1004&nbsp; &nbsp; &nbsp;11002&nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1001&nbsp; &nbsp; &nbsp; &nbsp; x&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1001&nbsp; &nbsp; &nbsp;11003&nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;1004&nbsp; &nbsp; &nbsp; 11005&nbsp; &nbsp; &nbsp; 2&nbsp;Thanks.

sergeibaklan · Answer

michaelask2019&nbsp;
That could be like
=IFERROR(INDEX(Sheet1!$C:$C,MATCH(Sheet2!$B2,Sheet1!$B:$B,0)),"")
Similar if they are different workbooks, not sheets.
&nbsp;

michaelask2019 · Answer

SergeiBaklan&nbsp;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?&nbsp;

sergeibaklan · Answer

michaelask2019&nbsp;
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.

Forum Discussion

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

3 Replies

Resources