 # Complex nesting formula

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.
5 Replies

# Re: Complex nesting formula

I'd create helper range at any place within workbook 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)``

# Re: Complex nesting formula

Thank you very much this worked perfect!!

# Re: Complex nesting formula

@Myagemtag , you are welcome

# Re: Complex nesting formula

Lol..ok now I have another similar question.
Same type scenario. Jan thru Dec columns, populated with dollar amounts.
In the YTD column I want it to populate what ever the last updated month. So if Jan is populated then it returns that cell, if March is populated it returns March's \$.
I have the following & it works for some of the cells.
=if(r25="","",lookup(ifna(index(s25:ac25,xmatch(,s25:ac25)-1),s25:s25),s25:ac25))
I really don't understand the logic very well so I kept playing with it till it populated. The formula works if up to Feb being populated but then it doesn't.
I've tried other combinations of the formula & the closest I got was it will populated the most resent data if that cell is higher then r25 (january) but if its lower then r25 i get either n/a error or spill error. Help!!

# Re: Complex nesting formula

If I understood the logic correctly that's like that could be

``=INDEX(R25:AC25,MAX(1,XMATCH(,R25:AC25)-1))``

As a comment, that's always better to illustrate the question with sample file