Formula Help

%3CLINGO-SUB%20id%3D%22lingo-sub-3415835%22%20slang%3D%22en-US%22%3EFormula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3415835%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20there%20I%20have%20a%20spreadsheet%20with%202%20tables.%20In%20one%20table%20I%20have%20the%20information%20about%20how%20many%20service%20hours%20a%20person%20has%20completed%20and%20their%20graduation%20year.%20In%20the%20next%20table%20I%20have%20the%20graduation%20year%20and%20how%20many%20hours%20total%20they%20need%20to%20complete.%20I%20want%20to%20create%20a%20formula%20that%20matches%20the%20graduation%20years%20together%20and%20then%20applies%20the%20total%20hours%20minus%20the%20hours%20served%20to%20get%20an%20answer%20of%20how%20many%20hours%20they%20need%20to%20complete.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20anyone%20can%20help%20with%20this%20I'd%20be%20grateful!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3415835%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3416026%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3416026%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1398667%22%20target%3D%22_blank%22%3E%40GriffiAN%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DG3-INDEX(%24C%243%3A%24C%2425%2CMATCH(1%2C(E3%3D%24A%243%3A%24A%2425)*(F3%3D%24B%243%3A%24B%2425)%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EMaybe%20with%20this%20formula%20for%20the%20dataset%20in%20the%20attached%20file.%20Enter%20the%20formula%20with%20ctrl%2Bshift%2Benter%20if%20you%20don't%20work%20with%20Office365%20or%202021.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hey there I have a spreadsheet with 2 tables. In one table I have the information about how many service hours a person has completed and their graduation year. In the next table I have the graduation year and how many hours total they need to complete. I want to create a formula that matches the graduation years together and then applies the total hours minus the hours served to get an answer of how many hours they need to complete.

 

If anyone can help with this I'd be grateful!

1 Reply

@GriffiAN 

=G3-INDEX($C$3:$C$25,MATCH(1,(E3=$A$3:$A$25)*(F3=$B$3:$B$25),0))

Maybe with this formula for the dataset in the attached file. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.