Home

Extract shared values between two columns

%3CLINGO-SUB%20id%3D%22lingo-sub-772680%22%20slang%3D%22en-US%22%3EExtract%20shared%20values%20between%20two%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-772680%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20extract%20shared%20values%20between%20two%20columns.%20I%20found%20a%20post%20on%20another%20website%20that%20has%20a%20formula%20to%20use%2C%20but%20I%20am%20struggling%20to%20apply%20it%20to%20my%20dataset.%20Below%20is%20the%20formula.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%3DINDEX(%3CSPAN%20class%3D%22s1%22%3E%24A%242%3A%24A%2411%3C%2FSPAN%3E%2C%20SMALL%3CSPAN%20class%3D%22s2%22%3E(%3C%2FSPAN%3EIF%3CSPAN%20class%3D%22s3%22%3E(%3C%2FSPAN%3ECOUNTIF%3CSPAN%20class%3D%22s4%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22s2%22%3E%24B%242%3A%24B%2411%3C%2FSPAN%3E%2C%20%3CSPAN%20class%3D%22s1%22%3E%24A%242%3A%24A%2411%3C%2FSPAN%3E%3CSPAN%20class%3D%22s4%22%3E)%3C%2FSPAN%3E%2C%20MATCH%3CSPAN%20class%3D%22s4%22%3E(%3C%2FSPAN%3EROW%3CSPAN%20class%3D%22s5%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22s1%22%3E%24A%242%3A%24A%2411%3C%2FSPAN%3E%3CSPAN%20class%3D%22s5%22%3E)%3C%2FSPAN%3E%2CROW%3CSPAN%20class%3D%22s5%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22s1%22%3E%24A%242%3A%24A%2411%3C%2FSPAN%3E%3CSPAN%20class%3D%22s5%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22s4%22%3E)%3C%2FSPAN%3E%2C%20%22%22%3CSPAN%20class%3D%22s3%22%3E)%3C%2FSPAN%3E%2C%20ROWS%3CSPAN%20class%3D%22s3%22%3E(%24A%241%3AA1)%3C%2FSPAN%3E%3CSPAN%20class%3D%22s2%22%3E)%3C%2FSPAN%3E)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThis%20only%20works%20between%20cells%202-11.%20I%20have%20tried%20changing%20the%20values%20in%20the%20formula%20to%20fit%20my%20target%20(column%20A%20has%20119%20values%20and%20column%20B%20has%20193)%2C%20but%20for%20some%20reason%2C%20all%20I%20get%20in%20return%20is%20%23VALUE!%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI'm%20not%20sure%20what%20I'm%20doing%20wrong%2C%20but%20ideally%2C%20I'd%20like%20to%20compare%20all%20119%20values%20in%20column%20A%20with%20the%20193%20values%20in%20column%20B%20and%20return%20the%20shared%20values%20in%20column%20C%2C%20or%20wherever%20I%20enter%20the%20formula.%20I%20am%20also%20figuring%20out%20how%20to%20apply%20this%20with%20various%20column%20lengths%20since%20I%20have%20a%20lot%20of%20columns%20to%20compare%2C%20and%20they%20all%20have%20different%20lengths.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThank%20you%20in%20advance%20for%20your%20help!%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-772680%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-772870%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20shared%20values%20between%20two%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-772870%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F381778%22%20target%3D%22_blank%22%3E%40MGreenfield%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20would%20help%20if%20you%20could%20upload%20your%20worksheet%20with%20some%20sample%20data%20in%20it.%20Please%20remove%2Fmask%20any%20sensitive%20data%20before%20upload.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-773164%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20shared%20values%20between%20two%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-773164%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F381778%22%20target%3D%22_blank%22%3E%40MGreenfield%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFormula%20assumes%20your%20data%20starts%20from%20the%20second%20row.%20You%20may%20expand%20your%20range%20with%20some%20gap%2C%20wrap%20formula%20with%20IFERROR%20and%20enter%20it%20in%20C2%20as%20an%20array%20formula%20(Ctrl%2BShift%2BEnter)%2C%20like%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(INDEX(%24A%242%3A%24A%241100%2C%20SMALL(IF(COUNTIF(%24B%242%3A%24B%241100%2C%20%24A%242%3A%24A%241100)%2C%20MATCH(ROW(%24A%242%3A%24A%241100)%2CROW(%24A%242%3A%24A%241100))%2C%20%22%22)%2C%20ROWS(%24A%241%3AA1)))%2C%22%22)%3C%2FPRE%3E%0A%3CP%3Eand%20drag%20it%20down%20till%20empty%20cells%20appear.%3C%2FP%3E%0A%3CP%3EAlternative%20non-array%20formula%20could%20be%20with%20AGGREGATE%20and%20with%20using%20of%20dynamic%20ranges%2C%20but%20that's%20not%20critical.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-774000%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20shared%20values%20between%20two%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-774000%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368896%22%20target%3D%22_blank%22%3E%40Kodipady%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttached%20is%20the%20document%20I'm%20working%20on.%20Thanks%20for%20your%20help!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-774280%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20shared%20values%20between%20two%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-774280%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F381778%22%20target%3D%22_blank%22%3E%40MGreenfield%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20A2%20and%20B2%20there%20are%20errors%20returned.%20If%20remove%2C%20formula%20in%20C2%20works.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-774297%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20shared%20values%20between%20two%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-774297%22%20slang%3D%22en-US%22%3E%3CP%3EAmazing!%20Thank%20you%20so%20much%20for%20your%20help!!%26nbsp%3B%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%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-774301%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20shared%20values%20between%20two%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-774301%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F381778%22%20target%3D%22_blank%22%3E%40MGreenfield%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
MGreenfield
New Contributor

Hi everyone, 

 

I am trying to extract shared values between two columns. I found a post on another website that has a formula to use, but I am struggling to apply it to my dataset. Below is the formula. 

 

=INDEX($A$2:$A$11, SMALL(IF(COUNTIF($B$2:$B$11, $A$2:$A$11), MATCH(ROW($A$2:$A$11),ROW($A$2:$A$11)), ""), ROWS($A$1:A1)))

 

This only works between cells 2-11. I have tried changing the values in the formula to fit my target (column A has 119 values and column B has 193), but for some reason, all I get in return is #VALUE! 

 

I'm not sure what I'm doing wrong, but ideally, I'd like to compare all 119 values in column A with the 193 values in column B and return the shared values in column C, or wherever I enter the formula. I am also figuring out how to apply this with various column lengths since I have a lot of columns to compare, and they all have different lengths. 

 

Thank you in advance for your help! 

6 Replies

@MGreenfield 

It would help if you could upload your worksheet with some sample data in it. Please remove/mask any sensitive data before upload.  

@MGreenfield 

Formula assumes your data starts from the second row. You may expand your range with some gap, wrap formula with IFERROR and enter it in C2 as an array formula (Ctrl+Shift+Enter), like

=IFERROR(INDEX($A$2:$A$1100, SMALL(IF(COUNTIF($B$2:$B$1100, $A$2:$A$1100), MATCH(ROW($A$2:$A$1100),ROW($A$2:$A$1100)), ""), ROWS($A$1:A1))),"")

and drag it down till empty cells appear.

Alternative non-array formula could be with AGGREGATE and with using of dynamic ranges, but that's not critical.

@Kodipady 

 

Attached is the document I'm working on. Thanks for your help! 

@MGreenfield 

In A2 and B2 there are errors returned. If remove, formula in C2 works.

Amazing! Thank you so much for your help!! @Sergei Baklan 

@MGreenfield , you are welcome

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies