SOLVED
Home

Excel formula help

%3CLINGO-SUB%20id%3D%22lingo-sub-277531%22%20slang%3D%22en-US%22%3EExcel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-277531%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3EI'm%20trying%20to%20create%20an%20excel%20formula%20and%20don't%20know%20how%20to%20start.%26nbsp%3B%20I%20know%20what%20I%20want%20it%20to%20do%2C%20but%20don't%20know%20how%20to%20make%20it%20work.%26nbsp%3B%20Please%20see%20the%20attached%20PDF.%26nbsp%3B%20Thanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDean%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-277531%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-321261%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-321261%22%20slang%3D%22en-US%22%3E%3CP%3Eto%20signify%20any%20formula%20first%20type%20%3D%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-293706%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-293706%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20sorry%2C%20but%20this%20is%20not%20easy!%3C%2FP%3E%3CP%3EYou%20need%20to%20rearrange%20the%20data%20in%20Teams%20sheet%20to%20make%20the%20players%20to%20next%20coach%20head.%3C%2FP%3E%3CP%3EYou%20can%20hide%20these%20extra%20columns%20or%20make%20their%20font%20color%20%3CSTRONG%3EWhite%3C%2FSTRONG%3E.%3C%2FP%3E%3CP%3EAfter%20that%2C%20you%20can%20use%20some%20sort%20of%20functions%20to%20link%20them%20to%20the%20Game%201%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20the%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-293143%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-293143%22%20slang%3D%22en-US%22%3E%3CP%3EOh%20my%20goodness.%26nbsp%3B%20That's%20incredible!%26nbsp%3B%20Thanks%20so%20much!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDean%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20another%20problem%20that%20I%20wanted%20to%20see%20if%20you%20could%20help%20with.%26nbsp%3B%20If%20not%2C%20that's%20ok.%26nbsp%3B%20Attached%20is%20another%20Excel%20sheet.%26nbsp%3B%20I%20want%20to%20be%20able%20to%20track%20teams%20playing%20against%20teams%20without%20having%20to%20copy%20paste%20everything%20each%20time.%26nbsp%3B%20If%20I%20plug%20in%20a%20coaches%20name%20in%20game%201%2C%20I%20want%20the%20players%20rostered.%26nbsp%3B%20Then%20when%20I%20put%20in%20the%20points%20for%20each%20player%2C%20I%20want%20that%20to%20put%20the%20number%20into%20the%20Totals%20tab%20so%20that%20can%20track%20the%20players%20points%20during%20the%20season.%26nbsp%3B%20Is%20this%20something%20that%20Excel%20can%20do%3F%26nbsp%3B%20Thanks!%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EDean%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-292531%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-292531%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20again%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20use%20this%20formula%20for%20each%20request%20column%3A%3C%2FP%3E%3CPRE%3E%3DIFERROR(INDEX(CONTACT!I%3AI%2CMATCH(B4%2CCONTACT!B%3AB%2C0))%26amp%3B%22%22%2C%22%22)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20it%20will%20return%20the%20full%20player%20name%20that%20corresponds%20to%20each%20member%20in%20the%20Contact%20sheet.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-292439%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-292439%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20have%20one%20more%20request.%26nbsp%3B%20Under%20the%20tab%206th-8th%20draft%2C%20I%20wanted%20to%20be%20able%20to%20add%20player%20requests%20that%20are%20linked%20with%20the%20player%20name%20to%20the%20player%20they%20want.%26nbsp%3B%20Is%20that%20possible%3F%26nbsp%3B%20So%20for%20example%20for%20Lauren%20Carter's%20team%2C%20in%20round%203%2C%20player%20Carter%20has%20a%20request%20for%20player%20Maunu%2C%20but%20that%20information%20is%20on%20the%20contact%20page.%26nbsp%3B%20Can%20that%20work%3F%26nbsp%3B%20Thanks!%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EDean%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-281826%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-281826%22%20slang%3D%22en-US%22%3E%3CP%3EGreat!%26nbsp%3B%20That's%20just%20amazing%20what%20you%20can%20do%20with%20Excel.%26nbsp%3B%20I%20need%20to%20learn%20more%20for%20sure!%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EDean%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-281815%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-281815%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20this%20case%2C%20all%20you%20have%20to%20do%20is%20expanding%20the%20ranges%20in%20the%20second%20part%20of%20the%20formula%20as%20follows%3A%3C%2FP%3E%3CPRE%3E%3DIFERROR(%3CBR%20%2F%3EINDEX('4th-5th%20Draft'!%24B%241%3A%24N%241%2CIFERROR(MATCH(C37%2C'4th-5th%20Draft'!%24B%244%3A%24N%244%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20IFERROR(MATCH(C37%2C'4th-5th%20Draft'!%24B%245%3A%24N%245%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20IFERROR(MATCH(C37%2C'4th-5th%20Draft'!%24B%246%3A%24N%246%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20IFERROR(MATCH(C37%2C'4th-5th%20Draft'!%24B%247%3A%24N%247%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20IFERROR(MATCH(C37%2C'4th-5th%20Draft'!%24B%248%3A%24N%248%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20IFERROR(MATCH(C37%2C'4th-5th%20Draft'!%24B%249%3A%24N%249%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20IFERROR(MATCH(C37%2C'4th-5th%20Draft'!%24B%2410%3A%24N%2410%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20MATCH(C37%2C'4th-5th%20Draft'!%24B%2411%3A%24N%2411%2C0)))))))))%2C%3CBR%20%2F%3EINDEX('4th-5th%20Draft'!%24B%2414%3A%24R%2414%2CIFERROR(MATCH(C37%2C'4th-5th%20Draft'!%24B%2417%3A%24R%2417%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20IFERROR(MATCH(C37%2C'4th-5th%20Draft'!%24B%2418%3A%24R%2418%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20IFERROR(MATCH(C37%2C'4th-5th%20Draft'!%24B%2419%3A%24R%2419%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20IFERROR(MATCH(C37%2C'4th-5th%20Draft'!%24B%2420%3A%24R%2420%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20IFERROR(MATCH(C37%2C'4th-5th%20Draft'!%24B%2421%3A%24R%2421%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20IFERROR(MATCH(C37%2C'4th-5th%20Draft'!%24B%2422%3A%24R%2422%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20IFERROR(MATCH(C37%2C'4th-5th%20Draft'!%24B%2423%3A%24R%2423%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20MATCH(C37%2C'4th-5th%20Draft'!%24B%2424%3A%24R%2424%2C0))))))))))%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20note%20that%20the%20Range%20is%20now%20from%20B%20to%20R%20instead%20of%20B%20to%20N%20as%20the%20previous%20one.%3C%2FP%3E%3CP%3EThe%20reason%20why%20I%20updated%20only%20the%20second%20Index%20is%20that%20you've%20added%20the%20new%20team%20next%20to%20the%20second%20group%20of%20teams%2C%20not%20below%20it%20as%20a%20new%20group.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20keep%20adding%20the%20new%20teams%20this%20way%20(to%20the%20right)%2C%20I%20can%20make%20the%20formula%20dynamic%20for%20you%20as%20follows%3A%3C%2FP%3E%3CPRE%3E%3DIFERROR(%3CBR%20%2F%3EINDEX('4th-5th%20Draft'!%241%3A%241%2CIFERROR(MATCH(C2%2C'4th-5th%20Draft'!%244%3A%244%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20IFERROR(MATCH(C2%2C'4th-5th%20Draft'!%245%3A%245%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20IFERROR(MATCH(C2%2C'4th-5th%20Draft'!%246%3A%246%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20IFERROR(MATCH(C2%2C'4th-5th%20Draft'!%247%3A%247%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20IFERROR(MATCH(C2%2C'4th-5th%20Draft'!%248%3A%248%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20IFERROR(MATCH(C2%2C'4th-5th%20Draft'!%249%3A%249%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20IFERROR(MATCH(C2%2C'4th-5th%20Draft'!%2410%3A%2410%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20MATCH(C2%2C'4th-5th%20Draft'!%2411%3A%2411%2C0)))))))))%2C%3CBR%20%2F%3EINDEX('4th-5th%20Draft'!%2414%3A%2414%2CIFERROR(MATCH(C2%2C'4th-5th%20Draft'!%2417%3A%2417%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20IFERROR(MATCH(C2%2C'4th-5th%20Draft'!%2418%3A%2418%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20IFERROR(MATCH(C2%2C'4th-5th%20Draft'!%2419%3A%2419%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20IFERROR(MATCH(C2%2C'4th-5th%20Draft'!%2420%3A%2420%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20IFERROR(MATCH(C2%2C'4th-5th%20Draft'!%2421%3A%2421%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20IFERROR(MATCH(C2%2C'4th-5th%20Draft'!%2422%3A%2422%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20IFERROR(MATCH(C2%2C'4th-5th%20Draft'!%2423%3A%2423%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20MATCH(C2%2C'4th-5th%20Draft'!%2424%3A%2424%2C0))))))))))%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20done%20by%20targeting%20the%20entire%20rows%20instead%20of%20a%20specific%20range%20of%20them.%3C%2FP%3E%3CP%3ENow%20you%20can%20add%20as%20many%20teams%20as%20you%20want%20to%20the%20right%20of%20the%20first%20or%20the%20second%20group.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-281750%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-281750%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Again%2C%3C%2FP%3E%3CP%3ESo%20I%20tried%20to%20add%20another%20team%20and%20wasn't%20able%20to%20do%20it.%26nbsp%3B%20Could%20you%20help%20with%20this%20and%20then%20maybe%20I'll%20understand%20it%20better%3F%26nbsp%3B%20Thanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDean%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-281511%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-281511%22%20slang%3D%22en-US%22%3EWow!!%20Ok%20it%E2%80%99ll%20take%20me%20some%20time%20to%20decipher%20this!%20Again%2C%20thanks!!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-281509%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-281509%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20can%20add%20other%20teams%2C%20but%20you%20need%20to%20expand%20the%20formula.%3C%2FP%3E%3CP%3EThis%20is%20the%20formula%2C%20and%20to%20see%20it%20click%20F2.%3C%2FP%3E%3CPRE%3E%3DIFERROR(%3CBR%20%2F%3EINDEX('6th-8th%20Draft'!%24B%241%3A%24N%241%2CIFERROR(MATCH(C2%2C'6th-8th%20Draft'!%24B%244%3A%24N%244%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20IFERROR(MATCH(C2%2C'6th-8th%20Draft'!%24B%245%3A%24N%245%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20IFERROR(MATCH(C2%2C'6th-8th%20Draft'!%24B%246%3A%24N%246%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20IFERROR(MATCH(C2%2C'6th-8th%20Draft'!%24B%247%3A%24N%247%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20IFERROR(MATCH(C2%2C'6th-8th%20Draft'!%24B%248%3A%24N%248%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20IFERROR(MATCH(C2%2C'6th-8th%20Draft'!%24B%249%3A%24N%249%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20IFERROR(MATCH(C2%2C'6th-8th%20Draft'!%24B%2410%3A%24N%2410%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20MATCH(C2%2C'6th-8th%20Draft'!%24B%2411%3A%24N%2411%2C0)))))))))%2C%3CBR%20%2F%3EINDEX('6th-8th%20Draft'!%24B%2414%3A%24N%2414%2CIFERROR(MATCH(C2%2C'6th-8th%20Draft'!%24B%2417%3A%24N%2417%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20IFERROR(MATCH(C2%2C'6th-8th%20Draft'!%24B%2418%3A%24N%2418%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20IFERROR(MATCH(C2%2C'6th-8th%20Draft'!%24B%2419%3A%24N%2419%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20IFERROR(MATCH(C2%2C'6th-8th%20Draft'!%24B%2420%3A%24N%2420%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20IFERROR(MATCH(C2%2C'6th-8th%20Draft'!%24B%2421%3A%24N%2421%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20IFERROR(MATCH(C2%2C'6th-8th%20Draft'!%24B%2422%3A%24N%2422%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20IFERROR(MATCH(C2%2C'6th-8th%20Draft'!%24B%2423%3A%24N%2423%2C0)%2C%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20MATCH(C2%2C'6th-8th%20Draft'!%24B%2424%3A%24N%2424%2C0))))))))))%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20used%20Two%20INDEXs%20and%20several%20MATCHs.%3C%2FP%3E%3CP%3EIndex%201%20used%20for%20team%201%20to%204.%3C%2FP%3E%3CP%3EIndex%202%20used%20for%20team%205%20to%208.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20the%20Match%20functions%20are%20used%20for%20rounds.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-281504%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-281504%22%20slang%3D%22en-US%22%3E%3CP%3ENo%20thank%20you!%3C%2FP%3E%3CP%3EBut%20the%20good%20practice%20in%20communities%20is%20to%20mark%20the%20best%20response%2C%20in%20order%20to%20close%20the%20conversation%20and%20let%20other%20people%20know%20that%20you%20got%20what%20you%20want%20and%20the%20problem%20is%20solved.%3C%2FP%3E%3CP%3EIn%20this%20community%2C%20there%20is%20a%20big%20button%20for%20this%20regard%20under%20each%20reply.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-281503%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-281503%22%20slang%3D%22en-US%22%3E%3CP%3EI%20do%20have%20a%20question.%26nbsp%3B%20If%20I%20want%20to%20add%20an%20additional%20team%20or%20if%20I%20want%20to%20add%20more%20players%2C%20is%20it%20a%20simple%20copy%2Fpaste%20or%20is%20there%20much%20more%20to%20do%3F%26nbsp%3B%20Could%20you%20write%20the%20formula%20down%20in%20your%20reply%20so%20I%20can%20see%20what%20it%20looks%20like%3F%26nbsp%3B%20When%20I%20click%20in%20the%20box%2C%20it%20only%20showed%20the%20iferror.%26nbsp%3B%20Thanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDean%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-281501%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-281501%22%20slang%3D%22en-US%22%3E%3CP%3EHoly%20cow!%26nbsp%3B%20That%20is%20just%20amazing!%26nbsp%3B%20You%20are%20super%20helpful%20and%20an%20incredible%20excel%20master.%26nbsp%3B%20I%20am%20so%20appreciative%20of%20your%20work!%26nbsp%3B%20Is%20there%20anything%20I%20can%20do%20to%20elevate%20your%20status%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDean%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-281499%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-281499%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20the%20difficult%20part!%3C%2FP%3E%3CP%3EAnyway%2C%20I've%20updated%20the%20workbook%20with%20another%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-281494%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-281494%22%20slang%3D%22en-US%22%3E%3CP%3EIt%20looks%20like%20it%20only%20did%20it%20for%20the%20first%20row.%26nbsp%3B%20If%20I%20change%20the%20name%20on%20the%20first%20row%20it%20works%2C%20but%20when%20I%20go%20to%20round%202%20or%20later%2C%20it%20doesn't%20work.%26nbsp%3B%20That's%20the%20trouble-shooting%20that%20I've%20found.%26nbsp%3B%20Thanks.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EDean%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-281489%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-281489%22%20slang%3D%22en-US%22%3E%3CP%3EWow%2C%20that's%20pretty%20good%2C%20but%20for%20some%20reason%20when%20I%20inputed%20some%20other%20names%2C%20it%20wouldn't%20populate.%26nbsp%3B%20Can%20you%20take%20a%20look%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDean%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-281487%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-281487%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20created%20the%20formula%20below%20in%20the%20column%20%3CSTRONG%3ECoach%3C%2FSTRONG%3E%20of%20the%20sheet%20%3CSTRONG%3ETeams%3C%2FSTRONG%3E.%3C%2FP%3E%3CPRE%3E%3DIFERROR(HLOOKUP(C2%2CCHOOSE(%7B1%3B2%7D%2C'6th-8th%20Draft'!%24B%244%3A%24N%244%2C'6th-8th%20Draft'!%24B%241%3A%24N%241)%2C2%2C0)%2C%3CBR%20%2F%3EHLOOKUP(C2%2CCHOOSE(%7B1%3B2%7D%2C'6th-8th%20Draft'!%24B%2417%3A%24N%2417%2C'6th-8th%20Draft'!%24B%2414%3A%24N%2414)%2C2%2C0))%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20check%20out%20the%20attached%20workbook.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-281478%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-281478%22%20slang%3D%22en-US%22%3E%3CP%3EHere%20is%20the%20file.%26nbsp%3B%20Thanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDean%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-281475%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-281475%22%20slang%3D%22en-US%22%3E%3CP%3EOkay%2C%20I'll%20try%20out!%3C%2FP%3E%3CP%3EBut%20I%20need%20the%20workbook%20to%20apply%20the%20solution!%3C%2FP%3E%3CP%3ESo%20if%20the%20data%20isn't%20sensitive%2C%20please%20attached%20it%20here%20in%20a%20reply.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20better%20understanding%20your%20data%2C%20I%20need%20you%20to%20explain%20more%20what%20the%20first%20name%20and%20last%20name%20in%20sheet%201%2C%20and%20what%20the%20round%20names%20and%20numbers%20in%20sheet%202%3F%3C%2FP%3E%3CP%3EIn%20general%2C%20w%3CSPAN%20class%3D%22short_text%22%3E%3CSPAN%20class%3D%22%22%3Ehat%20is%20this%20data%3F%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20addition%2C%20please%20note%20that%20the%20possible%20solution%20will%20depend%20on%20the%20name%20in%20%3CSTRONG%3ERound%201%20only%3C%2FSTRONG%3E%2C%20and%20will%20match%20it%20with%20the%20names%20in%20the%20%3CSTRONG%3ELast%20Names%3C%2FSTRONG%3E%20column%20of%20Sheet%201.%3C%2FP%3E%3CP%3EAnd%20if%20there%20is%20a%20match%2C%20it%20will%20return%20the%20Head%20Coach%20in%20the%20corresponding%20cell%20of%20the%20Coach%20column%20in%20Sheet%201.%3C%2FP%3E%3CP%3EI%20think%20you%20want%20to%20apply%20this%20for%20each%20team%20table%2C%20so%20I'll%20try%20that%20out%20as%20well.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-281333%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-281333%22%20slang%3D%22en-US%22%3EThank%20you%20so%20much%20for%20your%20response%20and%20thanks%20for%20taking%20the%20time%20to%20look%20at%20this.%20If%20I%20need%20to%20break%20things%20out%20further%2C%20how%20would%20it%20be%20organized%3F%20I%20need%20to%20page%20two%20to%20remain%20the%20way%20it%20is.%20If%20you%20want%20to%20change%20page%20one%2C%20let%20me%20know%20how%20it%20would%20be%20organized%3F%20Page%20two%20is%20where%20each%20coach%20will%20know%20what%20player%20they%20get%20and%20the%20number%20next%20to%20the%20player%20is%20already%20a%20vlookup.%20Thanks!!%3CBR%20%2F%3E%3CBR%20%2F%3EDean%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-281221%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-281221%22%20slang%3D%22en-US%22%3E%3CP%3EWell%2C%20but%20the%20data%20in%20the%20second%20sheet%20is%20not%20organized%20in%20a%20way%20that%20is%20easy%20to%20depend%20on%20in%20the%20VLOOKUP%20function.%3CBR%20%2F%3E%3CBR%20%2F%3ETo%20make%20the%20solution%20easier%2C%20you%20need%20to%20create%20a%20single%20table%20for%20all%20data%20in%20orders%20to%20be%20able%20to%20use%20the%20VLOOKUP%20or%20other%20lookup%20functions.%3CBR%20%2F%3EOtherwise%2C%20the%20solution%20will%20be%20difficult!%3CBR%20%2F%3E%3CBR%20%2F%3ECan%20you%20combine%20them%20in%20a%20table%20(tabular%20form)%20as%20the%20data%20in%20the%20first%20sheet%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-280962%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-280962%22%20slang%3D%22en-US%22%3E%3CP%3EYes%2C%20that%20is%20correct.%26nbsp%3B%20Anytime%20I%20put%20a%20name%20in%20the%20round%2C%20it%20should%20automatically%20associate%20the%20coach%20name%20and%20then%20put%20the%20head%20coach%20name%20in%20the%20other%20page.%26nbsp%3B%20Can%20you%20do%20that%3F%26nbsp%3B%20Thanks!%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EDean%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-280960%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-280960%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20the%20PDF%20you%20attached%2C%20let%20me%20understand%20that%20you're%20trying%20to%20do!%3C%2FP%3E%3CP%3EYou%20typed%20the%20last%20name%20in%20the%20cell%20next%20to%20Round%201%20in%20the%20second%20sheet%2C%20and%20if%20this%20name%20is%20matched%20with%20the%20last%20name%20in%20the%20first%20sheet%2C%20then%20you%20want%20to%20return%20the%20Head%20Coach%20in%20the%20corresponding%20cell%20in%20the%20first%20sheet%3F%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-280846%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-280846%22%20slang%3D%22en-US%22%3E%3CP%3EThat%20helped%2C%20but%20it%20didn't%20give%20me%20the%20necessary%20directions%20on%20what%20I%20wanted%20to%20do.%26nbsp%3B%20It%20just%20showed%20me%20an%20example%20and%20the%20example%20didn't%20match%20what%20I%20was%20trying%20to%20accomplish.%3C%2FP%3E%3CP%3ESo%20I'm%20going%20to%20try%20again.%26nbsp%3B%20If%20you%20have%20to%20direct%20me%20to%20a%20video%20showing%20me%20how%20to%20do%20it%2C%20vs%20explaining%20it%20to%20me%20or%20showing%20me%20out%20right%20how%20the%20formula%20will%20need%20to%20work%2C%20please%20don't%20respond.%26nbsp%3B%20Thank%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDe%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-277544%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-277544%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20will%20point%20you%20in%20the%20right%20direction!%3C%2FP%3E%3CP%3EYou%20just%20need%20to%20one%20of%20the%20lookup%20functions%20such%20as%20VLOOKUP.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22short_text%22%3E%3CSPAN%3ESo%2C%20please%20check%20out%20this%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2FVLOOKUP-function-0BBC8083-26FE-4963-8AB8-93A18AD188A1%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Elink%3C%2FA%3E!%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22short_text%22%3E%3CSPAN%3ERegards%2C%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22short_text%22%3E%3CSPAN%3EHaytham%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
testing out
Occasional Contributor

Hi all,

I'm trying to create an excel formula and don't know how to start.  I know what I want it to do, but don't know how to make it work.  Please see the attached PDF.  Thanks!

 

Dean

 

25 Replies
Highlighted

Hi,

 

I will point you in the right direction!

You just need to one of the lookup functions such as VLOOKUP.

So, please check out this link!

 

Regards,

Haytham

Highlighted

That helped, but it didn't give me the necessary directions on what I wanted to do.  It just showed me an example and the example didn't match what I was trying to accomplish.

So I'm going to try again.  If you have to direct me to a video showing me how to do it, vs explaining it to me or showing me out right how the formula will need to work, please don't respond.  Thank you.

 

De

Highlighted

Hi,

 

On the PDF you attached, let me understand that you're trying to do!

You typed the last name in the cell next to Round 1 in the second sheet, and if this name is matched with the last name in the first sheet, then you want to return the Head Coach in the corresponding cell in the first sheet??

Highlighted

Yes, that is correct.  Anytime I put a name in the round, it should automatically associate the coach name and then put the head coach name in the other page.  Can you do that?  Thanks!

Dean

Highlighted

Well, but the data in the second sheet is not organized in a way that is easy to depend on in the VLOOKUP function.

To make the solution easier, you need to create a single table for all data in orders to be able to use the VLOOKUP or other lookup functions.
Otherwise, the solution will be difficult!

Can you combine them in a table (tabular form) as the data in the first sheet?

Highlighted
Thank you so much for your response and thanks for taking the time to look at this. If I need to break things out further, how would it be organized? I need to page two to remain the way it is. If you want to change page one, let me know how it would be organized? Page two is where each coach will know what player they get and the number next to the player is already a vlookup. Thanks!!

Dean
Highlighted

Okay, I'll try out!

But I need the workbook to apply the solution!

So if the data isn't sensitive, please attached it here in a reply.

 

For better understanding your data, I need you to explain more what the first name and last name in sheet 1, and what the round names and numbers in sheet 2?

In general, what is this data?

 

In addition, please note that the possible solution will depend on the name in Round 1 only, and will match it with the names in the Last Names column of Sheet 1.

And if there is a match, it will return the Head Coach in the corresponding cell of the Coach column in Sheet 1.

I think you want to apply this for each team table, so I'll try that out as well.

 

Regards

Highlighted

Here is the file.  Thanks!

 

Dean

Highlighted

Hi,

 

I've created the formula below in the column Coach of the sheet Teams.

=IFERROR(HLOOKUP(C2,CHOOSE({1;2},'6th-8th Draft'!$B$4:$N$4,'6th-8th Draft'!$B$1:$N$1),2,0),
HLOOKUP(C2,CHOOSE({1;2},'6th-8th Draft'!$B$17:$N$17,'6th-8th Draft'!$B$14:$N$14),2,0))

 

Please check out the attached workbook.

Highlighted

Wow, that's pretty good, but for some reason when I inputed some other names, it wouldn't populate.  Can you take a look?

 

Dean

Highlighted

It looks like it only did it for the first row.  If I change the name on the first row it works, but when I go to round 2 or later, it doesn't work.  That's the trouble-shooting that I've found.  Thanks.


Dean

Highlighted
Solution

This is the difficult part!

Anyway, I've updated the workbook with another formula.

Highlighted

Holy cow!  That is just amazing!  You are super helpful and an incredible excel master.  I am so appreciative of your work!  Is there anything I can do to elevate your status?

 

Dean

Highlighted

I do have a question.  If I want to add an additional team or if I want to add more players, is it a simple copy/paste or is there much more to do?  Could you write the formula down in your reply so I can see what it looks like?  When I click in the box, it only showed the iferror.  Thanks.

 

Dean

Highlighted

No thank you!

But the good practice in communities is to mark the best response, in order to close the conversation and let other people know that you got what you want and the problem is solved.

In this community, there is a big button for this regard under each reply.

 

Regards

Highlighted

You can add other teams, but you need to expand the formula.

This is the formula, and to see it click F2.

=IFERROR(
INDEX('6th-8th Draft'!$B$1:$N$1,IFERROR(MATCH(C2,'6th-8th Draft'!$B$4:$N$4,0),
                                   IFERROR(MATCH(C2,'6th-8th Draft'!$B$5:$N$5,0),
                                   IFERROR(MATCH(C2,'6th-8th Draft'!$B$6:$N$6,0),
                                   IFERROR(MATCH(C2,'6th-8th Draft'!$B$7:$N$7,0),
                                   IFERROR(MATCH(C2,'6th-8th Draft'!$B$8:$N$8,0),
                                   IFERROR(MATCH(C2,'6th-8th Draft'!$B$9:$N$9,0),
                                   IFERROR(MATCH(C2,'6th-8th Draft'!$B$10:$N$10,0),
                                   MATCH(C2,'6th-8th Draft'!$B$11:$N$11,0))))))))),
INDEX('6th-8th Draft'!$B$14:$N$14,IFERROR(MATCH(C2,'6th-8th Draft'!$B$17:$N$17,0),
                                   IFERROR(MATCH(C2,'6th-8th Draft'!$B$18:$N$18,0),
                                   IFERROR(MATCH(C2,'6th-8th Draft'!$B$19:$N$19,0),
                                   IFERROR(MATCH(C2,'6th-8th Draft'!$B$20:$N$20,0),
                                   IFERROR(MATCH(C2,'6th-8th Draft'!$B$21:$N$21,0),
                                   IFERROR(MATCH(C2,'6th-8th Draft'!$B$22:$N$22,0),
                                   IFERROR(MATCH(C2,'6th-8th Draft'!$B$23:$N$23,0),
                                   MATCH(C2,'6th-8th Draft'!$B$24:$N$24,0))))))))))

 

I've used Two INDEXs and several MATCHs.

Index 1 used for team 1 to 4.

Index 2 used for team 5 to 8.

 

And the Match functions are used for rounds.

Highlighted
Wow!! Ok it’ll take me some time to decipher this! Again, thanks!!
Highlighted

Hi Again,

So I tried to add another team and wasn't able to do it.  Could you help with this and then maybe I'll understand it better?  Thanks!

 

Dean

Highlighted

Hi,

 

In this case, all you have to do is expanding the ranges in the second part of the formula as follows:

=IFERROR(
INDEX('4th-5th Draft'!$B$1:$N$1,IFERROR(MATCH(C37,'4th-5th Draft'!$B$4:$N$4,0),
                                   IFERROR(MATCH(C37,'4th-5th Draft'!$B$5:$N$5,0),
                                   IFERROR(MATCH(C37,'4th-5th Draft'!$B$6:$N$6,0),
                                   IFERROR(MATCH(C37,'4th-5th Draft'!$B$7:$N$7,0),
                                   IFERROR(MATCH(C37,'4th-5th Draft'!$B$8:$N$8,0),
                                   IFERROR(MATCH(C37,'4th-5th Draft'!$B$9:$N$9,0),
                                   IFERROR(MATCH(C37,'4th-5th Draft'!$B$10:$N$10,0),
                                   MATCH(C37,'4th-5th Draft'!$B$11:$N$11,0))))))))),
INDEX('4th-5th Draft'!$B$14:$R$14,IFERROR(MATCH(C37,'4th-5th Draft'!$B$17:$R$17,0),
                                   IFERROR(MATCH(C37,'4th-5th Draft'!$B$18:$R$18,0),
                                   IFERROR(MATCH(C37,'4th-5th Draft'!$B$19:$R$19,0),
                                   IFERROR(MATCH(C37,'4th-5th Draft'!$B$20:$R$20,0),
                                   IFERROR(MATCH(C37,'4th-5th Draft'!$B$21:$R$21,0),
                                   IFERROR(MATCH(C37,'4th-5th Draft'!$B$22:$R$22,0),
                                   IFERROR(MATCH(C37,'4th-5th Draft'!$B$23:$R$23,0),
                                   MATCH(C37,'4th-5th Draft'!$B$24:$R$24,0))))))))))

 

Please note that the Range is now from B to R instead of B to N as the previous one.

The reason why I updated only the second Index is that you've added the new team next to the second group of teams, not below it as a new group.

 

If you keep adding the new teams this way (to the right), I can make the formula dynamic for you as follows:

=IFERROR(
INDEX('4th-5th Draft'!$1:$1,IFERROR(MATCH(C2,'4th-5th Draft'!$4:$4,0),
                                   IFERROR(MATCH(C2,'4th-5th Draft'!$5:$5,0),
                                   IFERROR(MATCH(C2,'4th-5th Draft'!$6:$6,0),
                                   IFERROR(MATCH(C2,'4th-5th Draft'!$7:$7,0),
                                   IFERROR(MATCH(C2,'4th-5th Draft'!$8:$8,0),
                                   IFERROR(MATCH(C2,'4th-5th Draft'!$9:$9,0),
                                   IFERROR(MATCH(C2,'4th-5th Draft'!$10:$10,0),
                                   MATCH(C2,'4th-5th Draft'!$11:$11,0))))))))),
INDEX('4th-5th Draft'!$14:$14,IFERROR(MATCH(C2,'4th-5th Draft'!$17:$17,0),
                                   IFERROR(MATCH(C2,'4th-5th Draft'!$18:$18,0),
                                   IFERROR(MATCH(C2,'4th-5th Draft'!$19:$19,0),
                                   IFERROR(MATCH(C2,'4th-5th Draft'!$20:$20,0),
                                   IFERROR(MATCH(C2,'4th-5th Draft'!$21:$21,0),
                                   IFERROR(MATCH(C2,'4th-5th Draft'!$22:$22,0),
                                   IFERROR(MATCH(C2,'4th-5th Draft'!$23:$23,0),
                                   MATCH(C2,'4th-5th Draft'!$24:$24,0))))))))))

 

This is done by targeting the entire rows instead of a specific range of them.

Now you can add as many teams as you want to the right of the first or the second group.

 

Hope that helps

Highlighted

Great!  That's just amazing what you can do with Excel.  I need to learn more for sure!


Dean

Highlighted

Hi,

I have one more request.  Under the tab 6th-8th draft, I wanted to be able to add player requests that are linked with the player name to the player they want.  Is that possible?  So for example for Lauren Carter's team, in round 3, player Carter has a request for player Maunu, but that information is on the contact page.  Can that work?  Thanks!


Dean

Highlighted

Hi again,

 

You can use this formula for each request column:

=IFERROR(INDEX(CONTACT!I:I,MATCH(B4,CONTACT!B:B,0))&"","")

 

But it will return the full player name that corresponds to each member in the Contact sheet.

Highlighted

Oh my goodness.  That's incredible!  Thanks so much!

 

Dean

 

I have another problem that I wanted to see if you could help with.  If not, that's ok.  Attached is another Excel sheet.  I want to be able to track teams playing against teams without having to copy paste everything each time.  If I plug in a coaches name in game 1, I want the players rostered.  Then when I put in the points for each player, I want that to put the number into the Totals tab so that can track the players points during the season.  Is this something that Excel can do?  Thanks!


Dean

Highlighted

Hi,

 

I'm sorry, but this is not easy!

You need to rearrange the data in Teams sheet to make the players to next coach head.

You can hide these extra columns or make their font color White.

After that, you can use some sort of functions to link them to the Game 1 sheet.

 

Please see the attached file.

Highlighted

to signify any formula first type =

Related Conversations
Automatic Selling price calculator
nic140274 in Excel on
0 Replies
Search fuction
Alvaro1990 in Excel on
1 Replies
UNIQUE, FILTER function no more available
Fabien_M in Excel on
5 Replies
Pulling specific data from multiple worksheets into one
AmberDDowney in Excel on
1 Replies
Excel
Svein Tore Kristensen in Excel on
0 Replies
Excel Function Question
Nick_Dirienzo in Excel on
2 Replies