Complex nesting formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2044058%22%20slang%3D%22en-US%22%3EComplex%20nesting%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2044058%22%20slang%3D%22en-US%22%3EI%20need%20help%20figuring%20out%20the%20correct%20formula.%20I%20have%20a%20monthly%20report%20that%20shows%20monthly%20results%20as%20well%20as%20YE.%20The%20current%20month%20being%20reported%20needs%20to%20return%20a%20result%20%26amp%3B%20that%201%20cell%20needs%20to%20up%20date%20when%20the%20new%20monthly%20number%20is%20populated.%20Here's%20example%3A%3CBR%20%2F%3ECells%20G4%20thru%20R4%20(January%20thru%20December%20respectfully)%20is%20for%20individual%20month%20result.%20S4%20has%20my%20formula%20as%20follows%3CBR%20%2F%3E%3Difs(g4%26gt%3B%3D81%25%2C%225%22%2Cg4%26gt%3B%3D80%25%2C%224%22%2Cg4%26gt%3B%3D75%25%2C%223%22%2Cg4%26gt%3B%3D70%25%2C%222%22%2Cg4%26gt%3B1%25%2C%221%22%2Ctrue%2C%22%22)%3CBR%20%2F%3ESo%20that%20works%20if%20cell%20G4%20is%20populated%20but%20I%20want%20the%20formula%20in%20S4%20to%20look%20at%20R4%20%26amp%3B%20if%20thats%20blank%20then%20look%20at%20Q4%20and%20populate%20the%20appropriate%20rating%20as%20I%20have%20currently%20in%20S4.%20And%20so%20on%2C%20if%20R4%20is%20blank%20then%20look%20at%20P4.%20Bottom%20line%20i%20want%20it%20to%20populate%20rating%20based%20on%20G4%20if%20populated%2C%20Rate%20for%20H4%20if%20that%20is%20populated.%3CBR%20%2F%3EI%20hope%20this%20makes%20sense.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2044058%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-2044377%22%20slang%3D%22en-US%22%3ERe%3A%20Complex%20nesting%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2044377%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F922539%22%20target%3D%22_blank%22%3E%40Myagemtag%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'd%20create%20helper%20range%20at%20any%20place%20within%20workbook%3C%2FP%3E%0A%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%20474px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F244949i178C708DDE45E6D6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EWith%20it%20formula%20for%20latest%20filled%20month%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DLOOKUP(IFNA(INDEX(%24G%244%3A%24R%244%2CXMATCH(%2C%24G%244%3A%24R%244)-1)%2C%24R%244)%2C%24T%244%3A%24T%249%2C%24U%244%3A%24U%249)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor
I need help figuring out the correct formula. I have a monthly report that shows monthly results as well as YE. The current month being reported needs to return a result & that 1 cell needs to up date when the new monthly number is populated. Here's example:
Cells G4 thru R4 (January thru December respectfully) is for individual month result. S4 has my formula as follows
=ifs(g4>=81%,"5",g4>=80%,"4",g4>=75%,"3",g4>=70%,"2",g4>1%,"1",true,"")
So that works if cell G4 is populated but I want the formula in S4 to look at R4 & if thats blank then look at Q4 and populate the appropriate rating as I have currently in S4. And so on, if R4 is blank then look at P4. Bottom line i want it to populate rating based on G4 if populated, Rate for H4 if that is populated.
I hope this makes sense.
1 Reply

@Myagemtag 

I'd create helper range at any place within workbook

image.png

With it formula for latest filled month could be

=LOOKUP(IFNA(INDEX($G$4:$R$4,XMATCH(,$G$4:$R$4)-1),$R$4),$T$4:$T$9,$U$4:$U$9)