Excel, Formulas and Functions

%3CLINGO-SUB%20id%3D%22lingo-sub-3121056%22%20slang%3D%22en-US%22%3EExcel%2C%20Formulas%20and%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3121056%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20using%20Microsoft%20365%20with%20the%20latest%20version%20of%20Excel.%26nbsp%3B%20I%20have%20two%20columns%20of%20names%20of%20members%20of%20my%20church%20who%20have%20made%20contributions%20in%20years%202019%2C%202020%2C%20and%202021.%20Some%20of%20the%20folks%20did%20not%20make%20contributions%20in%20all%20three%20years%2C%20for%20various%20reasons.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20seeking%20to%20compare%20the%20names%20in%20Column%20A%20who%20made%20contributions%20in%202019%20to%20those%20who%20made%20contributions%20in%202020%20in%20Column%20C%20to%20identified%20matches%20in%20names%20for%202019%20and%202020.%20I%20have%20tried%20the%20Compare%20Columns%20function%20and%20the%20VLOOKUP%20function%20but%20to%20no%20avail.%20An%20example%20of%20my%20data%20is%20as%20seen%20below.%20What%20functions%20or%20combination%20of%20functions%20will%20achieve%20my%20goal%3F%3C%2FP%3E%3CTABLE%20width%3D%22776%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22288%22%3EGivers%20-%202019%3C%2FTD%3E%3CTD%20width%3D%22230%22%3E2019%20to%202020%3CBR%20%2F%3EGivers%20Comparison%3C%2FTD%3E%3CTD%20width%3D%22258%22%3EGivers%202020%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESusan%20Alexander%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EAnn%20Adams%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EJerry%20%26amp%3B%20Suzanne%20Alford%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EGregg%20Akin%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EElsie%20Jane%20Anderson%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ETina%20Allen%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EArmand%20%26amp%3B%20Angelina%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ENick%20Anderson%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20assist%20me%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3121056%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-3121103%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%2C%20Formulas%20and%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3121103%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1298081%22%20target%3D%22_blank%22%3E%40Conferlete7707%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(COUNT(SEARCH(D2%2C%24F%242%3A%24F%2417))%26gt%3B0%2C%22match%22%2C%22no%20match%22)%3C%2FP%3E%3CP%3E%3DIF(NOT(ISNA(VLOOKUP(D2%2C%24F%242%3A%24F%2417%2C1%2CFALSE)))%2C%22match%22%2C%22no%20match%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20with%20above%20formulas.%20However%20these%20formulas%20only%20compare%202%20years%20as%20in%20your%202019%20to%202020%20Givers%20Comparison.%20If%20you%20wanted%20to%20compare%20years%202019%2C%202020%20and%202021%20it%20would%20be%20required%20to%20adapt%20the%20formulas.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3121157%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%2C%20Formulas%20and%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3121157%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1298081%22%20target%3D%22_blank%22%3E%40Conferlete7707%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20you%20need%20a%20full%20membership%20list%20covering%20the%20years%20in%20which%20you%20have%20an%20interest.%26nbsp%3B%20A%20lookup%20will%20determine%20whether%20payment%20was%20received%20for%20each%20of%20the%20years%20of%20interest.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DCOUNTIFS(Givers2019%2CMemberList)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EThis%20might%20be%20expected%20to%20give%20rise%20to%201%20(paid)%20or%200%20(not%20found).%26nbsp%3B%20Conditional%20formatting%20could%20be%20used%20to%20pretty%20up%20the%20display%20of%20results.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20607px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F345574i95E8976E710C937D%2Fimage-dimensions%2F607x208%3Fv%3Dv2%22%20width%3D%22607%22%20height%3D%22208%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I am using Microsoft 365 with the latest version of Excel.  I have two columns of names of members of my church who have made contributions in years 2019, 2020, and 2021. Some of the folks did not make contributions in all three years, for various reasons.

 

I am seeking to compare the names in Column A who made contributions in 2019 to those who made contributions in 2020 in Column C to identified matches in names for 2019 and 2020. I have tried the Compare Columns function and the VLOOKUP function but to no avail. An example of my data is as seen below. What functions or combination of functions will achieve my goal?

Givers - 20192019 to 2020
Givers Comparison
Givers 2020
Susan Alexander Ann Adams
Jerry & Suzanne Alford Gregg Akin
Elsie Jane Anderson Tina Allen
Armand & Angelina Nick Anderson

 

Can you assist me?

1 Reply

@Conferlete7707 

I think you need a full membership list covering the years in which you have an interest.  A lookup will determine whether payment was received for each of the years of interest.

=COUNTIFS(Givers2019,MemberList)

This might be expected to give rise to 1 (paid) or 0 (not found).  Conditional formatting could be used to pretty up the display of results. 

image.png