Discussion Finding identical values in a different spreadsheet and copying over correlating values. in Excel
https://techcommunity.microsoft.com/t5/excel/finding-identical-values-in-a-different-spreadsheet-and-copying/m-p/722702#M33565
<P>I am not intimately familiar with Excel, nor do I use it very often, so I apologize if my question is poorly phrased. </P><P> </P><P>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.</P><P> </P><P>Spreadsheet 1 + Spreadsheet 2 --->Function---> Spreadsheet 2</P><P>1000 1 1005 x 1005 2</P><P>1001 1 1004 x 1004 1</P><P>1002 2 1001 x 1001 1</P><P>1003 0 </P><P>1004 1</P><P>1005 2</P><P> </P><P>Thanks.</P>Wed, 26 Jun 2019 20:27:15 GMTmichaelask20192019-06-26T20:27:15ZFinding identical values in a different spreadsheet and copying over correlating values.
https://techcommunity.microsoft.com/t5/excel/finding-identical-values-in-a-different-spreadsheet-and-copying/m-p/722702#M33565
<P>I am not intimately familiar with Excel, nor do I use it very often, so I apologize if my question is poorly phrased. </P><P> </P><P>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.</P><P> </P><P>Spreadsheet 1 + Spreadsheet 2 --->Function---> Spreadsheet 2</P><P>1000 1 1005 x 1005 2</P><P>1001 1 1004 x 1004 1</P><P>1002 2 1001 x 1001 1</P><P>1003 0 </P><P>1004 1</P><P>1005 2</P><P> </P><P>Thanks.</P>Wed, 26 Jun 2019 20:27:15 GMThttps://techcommunity.microsoft.com/t5/excel/finding-identical-values-in-a-different-spreadsheet-and-copying/m-p/722702#M33565michaelask20192019-06-26T20:27:15ZRe: Finding identical values in a different spreadsheet and copying over correlating values.
https://techcommunity.microsoft.com/t5/excel/finding-identical-values-in-a-different-spreadsheet-and-copying/m-p/722736#M33568
<P><LI-USER uid="367285"></LI-USER> </P>
<P>That could be like</P>
<PRE>=IFERROR(INDEX(Sheet1!$C:$C,MATCH(Sheet2!$B2,Sheet1!$B:$B,0)),"")</PRE>
<P>Similar if they are different workbooks, not sheets.</P>
<P> </P>Wed, 26 Jun 2019 20:38:03 GMThttps://techcommunity.microsoft.com/t5/excel/finding-identical-values-in-a-different-spreadsheet-and-copying/m-p/722736#M33568Sergei Baklan2019-06-26T20:38:03ZRe: Finding identical values in a different spreadsheet and copying over correlating values.
https://techcommunity.microsoft.com/t5/excel/finding-identical-values-in-a-different-spreadsheet-and-copying/m-p/725698#M33643
<P><LI-USER uid="521"></LI-USER> </P><P>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? </P>Thu, 27 Jun 2019 18:34:24 GMThttps://techcommunity.microsoft.com/t5/excel/finding-identical-values-in-a-different-spreadsheet-and-copying/m-p/725698#M33643michaelask20192019-06-27T18:34:24ZRe: Finding identical values in a different spreadsheet and copying over correlating values.
https://techcommunity.microsoft.com/t5/excel/finding-identical-values-in-a-different-spreadsheet-and-copying/m-p/725975#M33645
<P><LI-USER uid="367285"></LI-USER> </P>
<P>B and C are columns in Sheet1 with your data, you may change any other ones, e.g. $A:$A instead of $B:$B</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="image.png" style="width: 143px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/121513iF223099E400E84BA/image-size/large?v=1.0&px=999" role="button" title="image.png" alt="image.png" /></span></P>
<P>$B2 is the first cell in Sheet2 with your code</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="image.png" style="width: 223px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/121514i65EDEE28CB610E6B/image-size/large?v=1.0&px=999" role="button" title="image.png" alt="image.png" /></span></P>
<P>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.</P>Thu, 27 Jun 2019 18:58:51 GMThttps://techcommunity.microsoft.com/t5/excel/finding-identical-values-in-a-different-spreadsheet-and-copying/m-p/725975#M33645Sergei Baklan2019-06-27T18:58:51Z