SOLVED
Home

Help with Excel Formula and table lookup

%3CLINGO-SUB%20id%3D%22lingo-sub-811005%22%20slang%3D%22en-US%22%3EHelp%20with%20Excel%20Formula%20and%20table%20lookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-811005%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20help%20with%20a%20formulae%20in%20the%20%22Answer%22%20cell%20that%20checks%20the%20Width%20Value%20and%20the%20Length%20Value%20against%20the%20table%20of%20values%20to%20the%20right.%20It%20should%20then%20bring%20back%20the%20correct%20value.%20I%20have%20added%20examples%20below.%20I%20hope%20someone%20can%20assist.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CU%3EExamples%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EIf%20Width%20was%2060%20and%20Length%20was%20158%2C%20the%20answer%20would%20be%20120%3CBR%20%2F%3EIf%20Width%20was%2048%20and%20Length%20was%20150%2C%20the%20answer%20would%20be%20105%3CBR%20%2F%3EIf%20Width%20was%2048%20and%20Length%20was%2060%2C%20the%20answer%20would%20be%2075%20because%20it%20is%20up%20to%20and%20including%2060%20for%20the%20width%2C%20up%20to%20and%20including%2090%20for%20the%20length%3CBR%20%2F%3EIf%20Width%20was%2091%20and%20Length%20was%20245%2C%20the%20answer%20would%20be%20205%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EMany%20thanks%20in%20advance.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CFONT%20color%3D%22%230000FF%22%3E%3CSTRONG%3EWidth%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E68%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%230000FF%22%3E%3CSTRONG%3EWIDTH%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%230000FF%22%3E%3CSTRONG%3E60%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%230000FF%22%3E%3CSTRONG%3E90%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%230000FF%22%3E%3CSTRONG%3E120%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%230000FF%22%3E%3CSTRONG%3E150%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%230000FF%22%3E%3CSTRONG%3E180%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%230000FF%22%3E%3CSTRONG%3E210%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%230000FF%22%3E%3CSTRONG%3E240%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CSTRONG%3ELength%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E158%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%3CSTRONG%3ELength%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E90%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E75%3C%2FTD%3E%3CTD%3E100%3C%2FTD%3E%3CTD%3E130%3C%2FTD%3E%3CTD%3E160%3C%2FTD%3E%3CTD%3E190%3C%2FTD%3E%3CTD%3E220%3C%2FTD%3E%3CTD%3E260%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CSTRONG%3E%3CFONT%20color%3D%22%23339966%22%3EAnswer%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E145%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E120%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E90%3C%2FTD%3E%3CTD%3E115%3C%2FTD%3E%3CTD%3E145%3C%2FTD%3E%3CTD%3E175%3C%2FTD%3E%3CTD%3E205%3C%2FTD%3E%3CTD%3E235%3C%2FTD%3E%3CTD%3E275%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E150%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E105%3C%2FTD%3E%3CTD%3E130%3C%2FTD%3E%3CTD%3E160%3C%2FTD%3E%3CTD%3E190%3C%2FTD%3E%3CTD%3E220%3C%2FTD%3E%3CTD%3E250%3C%2FTD%3E%3CTD%3E290%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E180%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E120%3C%2FTD%3E%3CTD%3E145%3C%2FTD%3E%3CTD%3E175%3C%2FTD%3E%3CTD%3E205%3C%2FTD%3E%3CTD%3E235%3C%2FTD%3E%3CTD%3E265%3C%2FTD%3E%3CTD%3E305%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E210%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E135%3C%2FTD%3E%3CTD%3E160%3C%2FTD%3E%3CTD%3E190%3C%2FTD%3E%3CTD%3E220%3C%2FTD%3E%3CTD%3E250%3C%2FTD%3E%3CTD%3E280%3C%2FTD%3E%3CTD%3E320%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E240%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E150%3C%2FTD%3E%3CTD%3E175%3C%2FTD%3E%3CTD%3E205%3C%2FTD%3E%3CTD%3E235%3C%2FTD%3E%3CTD%3E265%3C%2FTD%3E%3CTD%3E295%3C%2FTD%3E%3CTD%3E335%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E%26nbsp%3B%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-811005%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-811183%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%20Formula%20and%20table%20lookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-811183%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394581%22%20target%3D%22_blank%22%3E%40pebbles1221%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this%20formula%3A%3C%2FP%3E%3CPRE%3E%3DINDEX(K4%3AQ9%2C%3CBR%20%2F%3EIFNA(IF(OR(ISNUMBER(MATCH(B3%2CJ4%3AJ9%2C0))%2CB3%26gt%3BMAX(J4%3AJ9))%2CMATCH(B3%2CJ4%3AJ9%2C1)%2CMATCH(B3%2CJ4%3AJ9%2C1)%2B1)%2C1)%2C%3CBR%20%2F%3EIFNA(IF(OR(ISNUMBER(MATCH(B2%2CK2%3AQ2%2C0))%2CB2%26gt%3BMAX(K2%3AQ2))%2CMATCH(B2%2CK2%3AQ2%2C1)%2CMATCH(B2%2CK2%3AQ2%2C1)%2B1)%2C1))%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F127518i234054FDC792EC89%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-08-19_18-47-13.png%22%20title%3D%222019-08-19_18-47-13.png%22%20%2F%3E%3C%2FSPAN%3E%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-811158%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%20Formula%20and%20table%20lookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-811158%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%2F394581%22%20target%3D%22_blank%22%3E%40pebbles1221%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAssuming%20your%20answer%20is%20in%20cell%20B5%2C%20try%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DINDEX(%24K%244%3A%24Q%249%2CMATCH(IF(CEILING.MATH(%24B%243%2C30)%26lt%3B90%2C90%2CIF(CEILING.MATH(%24B%243%2C30)%26gt%3B240%2C240%2CCEILING.MATH(%24B%243%2C30)))%2C%24J%244%3A%24J%249%2C0)%2CMATCH(IF(CEILING.MATH(%24B%242%2C30)%26lt%3B60%2C60%2CIF(CEILING.MATH(%24B%242%2C30)%26gt%3B240%2C240%2CCEILING.MATH(%24B%242%2C30)))%2C%24K%242%3A%24Q%242%2C0))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-811249%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%20Formula%20and%20table%20lookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-811249%22%20slang%3D%22en-US%22%3E%3CP%3EI%20cannot%20thank%20you%20enough.%20You%20are%20amazingly%20talented.%20Thank%20you.%20This%20is%20exactly%20what%20I%20was%20looking%20for.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-811250%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%20Formula%20and%20table%20lookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-811250%22%20slang%3D%22en-US%22%3EThank%20you%20very%20much%20for%20your%20support%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-811251%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%20Formula%20and%20table%20lookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-811251%22%20slang%3D%22en-US%22%3EMy%20pleasure!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-811895%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Excel%20Formula%20and%20table%20lookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-811895%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394581%22%20target%3D%22_blank%22%3E%40pebbles1221%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECouple%20of%20more%20variants%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DINDEX(%24F%244%3A%24L%249%2C%0A%20%20%20%20IFERROR(AGGREGATE(15%2C6%2C1%2F(%24E%244%3A%24E%249%26gt%3B%3D%24B%243)*ROW(%24E%244%3A%24E%249)-ROW(%24E%243)%2C1)%2C6)%2C%0A%20%20%20%20IFERROR(AGGREGATE(15%2C6%2C1%2F(%24F%242%3A%24L%242%26gt%3B%3D%24B%242)*COLUMN(%24F%242%3A%24L%242)-COLUMN(%24E%242)%2C1)%2C7))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20for%20this%20specific%20range%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3D75%2B%0A%20%20%20(B2%26gt%3B%3D60)*((CEILING.MATH(B2%2F60*2)-2)*25%2B5*(B2%26gt%3B60)%2B5*(B2%26gt%3B210))%2B%0A%20%20%20(B3%26gt%3B%3D90)*(MIN(CEILING.MATH(B3%2F90*3)%2C8)-3)*15%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
pebbles1221
New Contributor

I need help with a formulae in the "Answer" cell that checks the Width Value and the Length Value against the table of values to the right. It should then bring back the correct value. I have added examples below. I hope someone can assist.

 

Examples

If Width was 60 and Length was 158, the answer would be 120
If Width was 48 and Length was 150, the answer would be 105
If Width was 48 and Length was 60, the answer would be 75 because it is up to and including 60 for the width, up to and including 90 for the length
If Width was 91 and Length was 245, the answer would be 205

 

Many thanks in advance.

 

                  
Width68       WIDTH6090120150180210240 
Length158       Length        
         9075100130160190220260 
Answer145       12090115145175205235275 
         150105130160190220250290 
         180120145175205235265305 
         210135160190220250280320 
         240150175205235265295335 
                  
6 Replies

Hello @pebbles1221,

 

Assuming your answer is in cell B5, try this:

 

=INDEX($K$4:$Q$9,MATCH(IF(CEILING.MATH($B$3,30)<90,90,IF(CEILING.MATH($B$3,30)>240,240,CEILING.MATH($B$3,30))),$J$4:$J$9,0),MATCH(IF(CEILING.MATH($B$2,30)<60,60,IF(CEILING.MATH($B$2,30)>240,240,CEILING.MATH($B$2,30))),$K$2:$Q$2,0))

Solution

@pebbles1221

 

Hi,

 

Try this formula:

=INDEX(K4:Q9,
IFNA(IF(OR(ISNUMBER(MATCH(B3,J4:J9,0)),B3>MAX(J4:J9)),MATCH(B3,J4:J9,1),MATCH(B3,J4:J9,1)+1),1),
IFNA(IF(OR(ISNUMBER(MATCH(B2,K2:Q2,0)),B2>MAX(K2:Q2)),MATCH(B2,K2:Q2,1),MATCH(B2,K2:Q2,1)+1),1))

2019-08-19_18-47-13.png

 

Regards

I cannot thank you enough. You are amazingly talented. Thank you. This is exactly what I was looking for.

Thank you very much for your support
My pleasure!

@pebbles1221 

Couple of more variants

=INDEX($F$4:$L$9,
    IFERROR(AGGREGATE(15,6,1/($E$4:$E$9>=$B$3)*ROW($E$4:$E$9)-ROW($E$3),1),6),
    IFERROR(AGGREGATE(15,6,1/($F$2:$L$2>=$B$2)*COLUMN($F$2:$L$2)-COLUMN($E$2),1),7))

and for this specific range

=75+
   (B2>=60)*((CEILING.MATH(B2/60*2)-2)*25+5*(B2>60)+5*(B2>210))+
   (B3>=90)*(MIN(CEILING.MATH(B3/90*3),8)-3)*15
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
21 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies