Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-722702%22%20slang%3D%22en-US%22%3EFinding%20identical%20values%20in%20a%20different%20spreadsheet%20and%20copying%20over%20correlating%20values.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-722702%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20not%20intimately%20familiar%20with%20Excel%2C%20nor%20do%20I%20use%20it%20very%20often%2C%20so%20I%20apologize%20if%20my%20question%20is%20poorly%20phrased.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20two%20spreadsheets.%20Spreadsheet%201%20has%20a%20full%20list%20of%20numerical%20codes%20in%20one%20column%20and%20a%20set%20of%20corresponding%20numbers%20in%20a%20different%20column.%20Spreadsheet%202%20has%20a%20column%20containing%20a%20subset%20of%20the%20codes%20from%20Spreadsheet%201%2C%20though%20they%20are%20in%20a%20different%20order.%20Spreadsheet%202%20also%20has%20a%20column%20that%20needs%20to%20be%20filled%20with%20the%20corresponding%20numbers%20from%20Spreadsheet%201.%20A%20method%20of%20doing%20so%20automatically%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESpreadsheet%201%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%2B%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Spreadsheet%202%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B---%26gt%3BFunction---%26gt%3B%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BSpreadsheet%202%3C%2FP%3E%3CP%3E1000%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%201%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%201005%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20x%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B1005%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B2%3C%2FP%3E%3CP%3E1001%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%201%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%201004%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20x%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B1004%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B1%3C%2FP%3E%3CP%3E1002%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%202%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%201001%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20x%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B1001%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B1%3C%2FP%3E%3CP%3E1003%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%200%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E1004%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%201%3C%2FP%3E%3CP%3E1005%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%202%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-722702%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-722736%22%20slang%3D%22en-US%22%3ERe%3A%20Finding%20identical%20values%20in%20a%20different%20spreadsheet%20and%20copying%20over%20correlating%20values.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-722736%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F367285%22%20target%3D%22_blank%22%3E%40michaelask2019%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%20like%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(INDEX(Sheet1!%24C%3A%24C%2CMATCH(Sheet2!%24B2%2CSheet1!%24B%3A%24B%2C0))%2C%22%22)%3C%2FPRE%3E%0A%3CP%3ESimilar%20if%20they%20are%20different%20workbooks%2C%20not%20sheets.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-725698%22%20slang%3D%22en-US%22%3ERe%3A%20Finding%20identical%20values%20in%20a%20different%20spreadsheet%20and%20copying%20over%20correlating%20values.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-725698%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20the%20response.%20Seeing%20the%20function%20has%20shown%20me%20just%20how%20little%20I%20know%20about%20Excel.%20Which%20portions%20of%20the%20function%20would%20be%20changed%20to%20match%20a%20specific%20spreadsheet%3F%20I'd%20imagine%20Sheet1%20and%20Sheet2%20are%20changed%20to%20the%20proper%20names%2C%20but%20what%20about%20%24C%20or%20%24B2%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-725975%22%20slang%3D%22en-US%22%3ERe%3A%20Finding%20identical%20values%20in%20a%20different%20spreadsheet%20and%20copying%20over%20correlating%20values.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-725975%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F367285%22%20target%3D%22_blank%22%3E%40michaelask2019%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EB%20and%20C%20are%20columns%20in%20Sheet1%20with%20your%20data%2C%20you%20may%20change%20any%20other%20ones%2C%20e.g.%20%24A%3A%24A%20instead%20of%20%24B%3A%24B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20143px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F121513iF223099E400E84BA%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%24B2%20is%20the%20first%20cell%20in%20Sheet2%20with%20your%20code%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20223px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F121514i65EDEE28CB610E6B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EFormula%20against%20it%20in%20C2.%20If%20you%20drag%20cell%20C2%20down%2C%20formula%20in%20C3%20will%20be%20with%20%24B3.%20Please%20check%20about%20absolute%20and%20relative%20references%20in%20Excel.%3C%2FP%3E%3C%2FLINGO-BODY%3E
michaelask2019
New Contributor

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

@michaelask2019 

That could be like

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

Similar if they are different workbooks, not sheets.

 

@Sergei Baklan 

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? 

@michaelask2019 

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

image.png

$B2 is the first cell in Sheet2 with your code

image.png

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.

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
201 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies