Help wanted with an array function with many criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-1189753%22%20slang%3D%22en-US%22%3EHelp%20wanted%20with%20an%20array%20function%20with%20many%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1189753%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20eternally%20grateful%20if%20someone%20could%20take%20some%20time%20and%20assist%20me%20with%20sorting%20out%20an%20array%20function%20that%20I%20got.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3EArrayFormula%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3EIF%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3EWEEKDAY%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%3EG2%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22number%22%3E2%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3EINDEX%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%3E'Merged%20sheet'!A3%3AA127%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3EMATCH%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22number%22%3E1%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3EAND%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%3EH6%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E%26gt%3B%3C%2FSPAN%3E%3CSPAN%3E'Merged%20sheet'!G3%3AG127%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%3EH6%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E%26gt%3B%3C%2FSPAN%3E%3CSPAN%3E'Merged%20sheet'!I3%3AI127%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E*%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3EAND%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSTRONG%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3EIF%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E'Merged%20sheet'!H3%3AH127%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E%26gt%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22number%22%3E0%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3EH6%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E%26lt%3B%3C%2FSPAN%3E'Merged%20sheet'!H3%3AH127%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3EIF%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E'Merged%20sheet'!J3%3AJ127%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E%26gt%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22number%22%3E0%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3EH6%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E%26lt%3B%3C%2FSPAN%3E'Merged%20sheet'!J3%3AJ127%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3C%2FSTRONG%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22number%22%3E0%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3EWhat%20I%20am%20trying%20to%20do%20is%20to%20pull%20names%20from%20column%20A%20in%20%22Merged%20Sheet%22%20based%20on%20criteria%20in%20the%20other%20columns%20mentioned.%20The%20issue%20I%20have%20is%20that%20the%20part%20(see%20bold%20above)%20where%20I%20require%20the%20values%20in%20the%20H%20and%20J%20columns%20(Merged%20sheet)%20to%20be%20non-empty%20(I%20used%20%26gt%3B0).%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3ESo%20I%20have%20two%20questions%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E1.)%20Have%20I%20used%20the%20right%20logic%20for%20the%20issue%20at%20hand%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E2.)%20What%20is%20wrong%20with%20my%20formula%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3EThank%20you%20so%20much%20for%20your%20help!%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3EAll%20the%20best%2C%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3ERoger%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1189753%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1189839%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20wanted%20with%20an%20array%20function%20with%20many%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1189839%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F566174%22%20target%3D%22_blank%22%3E%40Ariiiiba123123%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20would%20be%20difficult%20to%20digest%20this%20formula%20without%20any%20data.%20Perhaps%20you%20could%20share%20your%20workbook%20to%20help%20provide%20some%20context%20(Please%20remove%20any%20sensitive%20data%20-%20of%20course).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1189864%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20wanted%20with%20an%20array%20function%20with%20many%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1189864%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F566174%22%20target%3D%22_blank%22%3E%40Ariiiiba123123%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat's%20an%20odd%20nesting%20of%20functions%20within%20the%20formula.%20A%20sample%20would%20help%20tremendously.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1189996%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20wanted%20with%20an%20array%20function%20with%20many%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1189996%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F566174%22%20target%3D%22_blank%22%3E%40Ariiiiba123123%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20understood%20the%20logic%20of%20your%20formula%20correctly%2C%20perhaps%20this%20non-array%20formula%20returns%20your%20desired%20result%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(WEEKDAY(G2)%3D2%2C%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3EIFNA(LOOKUP(2%2C1%2FFREQUENCY(1%2C%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E('Merged%20sheet'!G3%3AG127%3C%2FSTRONG%3E%3C%2FP%3E%3CH6%20id%3D%22toc-hId--6674051%22%20id%3D%22toc-hId--6674051%22%20id%3D%22toc-hId--6674051%22%20id%3D%22toc-hId--6674051%22%20id%3D%22toc-hId--6674051%22%20id%3D%22toc-hId--6674051%22%20id%3D%22toc-hId--6674051%22%20id%3D%22toc-hId--6674051%22%20id%3D%22toc-hId--6674051%22%20id%3D%22toc-hId--6674056%22%3E%3CP%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E('Merged%20sheet'!H3%3AH127%26lt%3B%26gt%3B%22%22)*%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E('Merged%20sheet'!H3%3AH127%26gt%3BH6)*%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E('Merged%20sheet'!I3%3AI127%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FH6%3E%3CH6%20id%3D%22toc-hId--1814128514%22%20id%3D%22toc-hId--1814128514%22%20id%3D%22toc-hId--1814128514%22%20id%3D%22toc-hId--1814128514%22%20id%3D%22toc-hId--1814128514%22%20id%3D%22toc-hId--1814128514%22%20id%3D%22toc-hId--1814128514%22%20id%3D%22toc-hId--1814128514%22%20id%3D%22toc-hId--1814128514%22%20id%3D%22toc-hId--1814128519%22%3E%3CP%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E('Merged%20sheet'!J3%3AJ127%26lt%3B%26gt%3B%22%22)*%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E('Merged%20sheet'!J3%3AJ127%26gt%3BH6))%2C%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E'Merged%20sheet'!A3%3AA127)%2C%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%22No%20Match%22)%2C%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%22Weekday%20is%20not%20Monday%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENote%20that%20the%20foregoing%20formula%20returns%20the%20name%20corresponding%20to%20the%20%3CSTRONG%3Efirst%20match%3C%2FSTRONG%3E%2C%20if%20the%20weekday%20of%20the%20Date%20in%20G2%20is%20%3CSTRONG%3EMonday%3C%2FSTRONG%3E%3B%20otherwise%2C%20it%20returns%20the%20text%20%22%3CSTRONG%3EWeekday%20is%20not%20Monday%3C%2FSTRONG%3E%22.%20If%20no%20match%20is%20found%2C%20it%20returns%20the%20text%20%22%3CSTRONG%3ENo%20Match%3C%2FSTRONG%3E%22.%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20have%20to%20precede%20dollar%20sign%20(%3CSTRONG%3E%24%3C%2FSTRONG%3E)%20to%20the%20row%20numbers%20of%20the%20references%20to%20the%20Merged%20sheet%2C%20if%20you%20intend%20to%20%3CEM%3Ecopy%20the%20formula%20down%20rows%3C%2FEM%3E.%26nbsp%3B%3C%2FP%3E%3C%2FH6%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1190215%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20wanted%20with%20an%20array%20function%20with%20many%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1190215%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%26nbsp%3B%20Hi!%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20very%20much%20for%20your%20reply.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20a%20sanitized%20version%20of%20my%20workbook.%20I%20hope%20it%20helps.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20relevant%20formula%20is%20in%20sheet%20%22Background%20calculations%22%2C%20B4%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAgain%2C%20thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1190218%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20wanted%20with%20an%20array%20function%20with%20many%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1190218%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3BThank%20you%20for%20your%20reply!%20I%20had%20understood%20that%20I%20need%20array%20formulas%20for%20index%2Bmatch%20formulas%20with%20several%20criteria%3F%20I%20will%20give%20your%20solution%20a%20try%20and%20see%20if%20I%20can%20get%20it%20to%20work.%20I%20have%20also%20uploaded%20a%20workbook%20in%20this%20thread%2C%20maybe%20that%20makes%20it%20easier%20to%20understand%20the%20issue!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1190220%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20wanted%20with%20an%20array%20function%20with%20many%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1190220%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F428790%22%20target%3D%22_blank%22%3E%40Patrick2788%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Patrick%2C%20thanks%20for%20your%20reply!%3C%2FP%3E%3CP%3EI%20am%20new%20with%20this%20so%20might%20be%20that%20I%20used%20the%20wrong%20formula%20or%20wrong%20nesting...%20Anyway%2C%20I%20uploaded%20a%20sheet%20for%20you%20to%20look%20at%20if%20you%20dont%20mind!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAll%20the%20best%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1191414%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20wanted%20with%20an%20array%20function%20with%20many%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1191414%22%20slang%3D%22en-US%22%3EI%E2%80%99ll%20defer%20looking%20into%20your%20sample%20file%20until%20such%20time%20that%20I%20receive%20feedback%20from%20you%20upon%20using%20my%20suggested%20formula.%3CBR%20%2F%3ELike%20you%20did%2C%20I%E2%80%99ll%20thereafter%20try%20looking%20into%20your%20sample%20file!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1193120%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20wanted%20with%20an%20array%20function%20with%20many%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1193120%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3BDear%20Twifoo%2C%20thank%20you%20so%20much%20for%20taking%20your%20time%2C%20again.%20I%20did%20try%20out%20the%20formula%20you%20suggested%20me%20but%20I%20get%20a%20%22No%20Natch%22%20value%20in%20return%20although%20this%20should%20not%20be%20the%20case.%20I%20believe%20that%20the%20issue%20might%20be%20that%20the%20values%20where%20the%20cells%20should%20be%20%26lt%3B%26gt%3B0%20should%20not%20return%20negative%20but%20rather%20positive%2C%20if%20that%20makes%20sense%20to%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet%20me%20just%20quickly%20describe%20what%20I%20want%20to%20do%20again%20as%20I%20believe%20I%20was%20not%20so%20clear%20about%20it%20before%3A%3C%2FP%3E%3CP%3EThe%20chosen%20time%20has%20to%20be%20within%20the%202%20slots%20outlined%20in%20the%20%22merged%20sheet%22.%20With%20the%20%26gt%3B0%20it%20is%20no%20issue%20if%20the%20cells%20are%20empty%20as%20it%20will%20return%20a%20positive%20value%20nonetheless%3B%20however%2C%20the%20%26lt%3B0%20values%20are%20a%20problem%20as%20the%20empty%20cells%20will%20make%20the%20formula%20return%20a%20negative%20result%2C%20although%20any%20number%20should%20be%20returning%20a%20positive%20in%20that%20case%2C%20as%20the%20second%20slot%20does%20in%20that%20case%20not%20exist.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet%20me%20know%20if%20I%20can%20clarify%20my%20intentions%20further!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1195064%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20wanted%20with%20an%20array%20function%20with%20many%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1195064%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F566174%22%20target%3D%22_blank%22%3E%40Ariiiiba123123%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20try%20to%20apply%20my%20suggested%20formula%20in%20your%20sample%20file%2C%20specify%20your%20expected%20result%2C%20then%20I'll%20see%20what%20I%20can%20do.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1195099%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20wanted%20with%20an%20array%20function%20with%20many%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1195099%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3BI%20have%20tried%20your%20formula%20and%20it%20wrongly%20returns%20%22No%20match%22.%20Potentially%20because%20the%20H%20and%20J%20columns%20are%20not%20always%20empty%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1195253%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20wanted%20with%20an%20array%20function%20with%20many%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1195253%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F566174%22%20target%3D%22_blank%22%3E%40Ariiiiba123123%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20enter%20the%20formula%20in%20the%20desired%20cell%20and%20specify%20what%20should%20be%20your%20expected%20result.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello everyone,

 

I would be eternally grateful if someone could take some time and assist me with sorting out an array function that I got.

 

=ArrayFormula(IF(WEEKDAY(G2)=2,INDEX('Merged sheet'!A3:A127,MATCH(1,AND((H6>'Merged sheet'!G3:G127),(H6>'Merged sheet'!I3:I127))*AND(IF('Merged sheet'!H3:H127>0,(H6<'Merged sheet'!H3:H127)),(IF('Merged sheet'!J3:J127)>0,H6<'Merged sheet'!J3:J127)),0))))

 

What I am trying to do is to pull names from column A in "Merged Sheet" based on criteria in the other columns mentioned. The issue I have is that the part (see bold above) where I require the values in the H and J columns (Merged sheet) to be non-empty (I used >0).

 

So I have two questions:

1.) Have I used the right logic for the issue at hand?

2.) What is wrong with my formula?

 

Thank you so much for your help!

 

All the best,

Roger

11 Replies

Hello @Ariiiiba123123,

 

It would be difficult to digest this formula without any data. Perhaps you could share your workbook to help provide some context (Please remove any sensitive data - of course).

@Ariiiiba123123 

That's an odd nesting of functions within the formula. A sample would help tremendously.

@Ariiiiba123123 

If I understood the logic of your formula correctly, perhaps this non-array formula returns your desired result: 

=IF(WEEKDAY(G2)=2,

IFNA(LOOKUP(2,1/FREQUENCY(1,

('Merged sheet'!G3:G127<H6)*

('Merged sheet'!H3:H127<>"")*

('Merged sheet'!H3:H127>H6)*

('Merged sheet'!I3:I127<H6)*

('Merged sheet'!J3:J127<>"")*

('Merged sheet'!J3:J127>H6)),

'Merged sheet'!A3:A127),

"No Match"),

"Weekday is not Monday")

 

Note that the foregoing formula returns the name corresponding to the first match, if the weekday of the Date in G2 is Monday; otherwise, it returns the text "Weekday is not Monday". If no match is found, it returns the text "No Match". 

You may have to precede dollar sign ($) to the row numbers of the references to the Merged sheet, if you intend to copy the formula down rows

@PReagan  Hi!

Thank you very much for your reply. 

 

I have attached a sanitized version of my workbook. I hope it helps. 

 

The relevant formula is in sheet "Background calculations", B4

 

Again, thanks!

@Twifoo Thank you for your reply! I had understood that I need array formulas for index+match formulas with several criteria? I will give your solution a try and see if I can get it to work. I have also uploaded a workbook in this thread, maybe that makes it easier to understand the issue!

@Patrick2788 

 

Hi Patrick, thanks for your reply!

I am new with this so might be that I used the wrong formula or wrong nesting... Anyway, I uploaded a sheet for you to look at if you dont mind!

 

All the best

I’ll defer looking into your sample file until such time that I receive feedback from you upon using my suggested formula.
Like you did, I’ll thereafter try looking into your sample file!

@Twifoo Dear Twifoo, thank you so much for taking your time, again. I did try out the formula you suggested me but I get a "No Natch" value in return although this should not be the case. I believe that the issue might be that the values where the cells should be <>0 should not return negative but rather positive, if that makes sense to you.

 

Let me just quickly describe what I want to do again as I believe I was not so clear about it before:

The chosen time has to be within the 2 slots outlined in the "merged sheet". With the >0 it is no issue if the cells are empty as it will return a positive value nonetheless; however, the <0 values are a problem as the empty cells will make the formula return a negative result, although any number should be returning a positive in that case, as the second slot does in that case not exist.

 

Let me know if I can clarify my intentions further!

@Ariiiiba123123 

Please try to apply my suggested formula in your sample file, specify your expected result, then I'll see what I can do. 

@Twifoo I have tried your formula and it wrongly returns "No match". Potentially because the H and J columns are not always empty

@Ariiiiba123123 

Please enter the formula in the desired cell and specify what should be your expected result.