Discussion Re: Complex nesting formula in Excel
https://techcommunity.microsoft.com/t5/excel/complex-nesting-formula/m-p/2108575#M87662
<P><LI-USER uid="922539"></LI-USER> </P>
<P>If I understood the logic correctly that's like</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="image.png" style="width: 816px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/250672iDC6DB133CC103841/image-size/large?v=v2&px=999" role="button" title="image.png" alt="image.png" /></span></P>
<P>that could be</P>
<LI-CODE lang="excel">=INDEX(R25:AC25,MAX(1,XMATCH(,R25:AC25)-1))</LI-CODE>
<P>As a comment, that's always better to illustrate the question with sample file</P>Fri, 29 Jan 2021 20:54:06 GMTSergei Baklan2021-01-29T20:54:06ZComplex nesting formula
https://techcommunity.microsoft.com/t5/excel/complex-nesting-formula/m-p/2044058#M85362
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:<BR />Cells G4 thru R4 (January thru December respectfully) is for individual month result. S4 has my formula as follows<BR />=ifs(g4>=81%,"5",g4>=80%,"4",g4>=75%,"3",g4>=70%,"2",g4>1%,"1",true,"")<BR />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.<BR />I hope this makes sense.Fri, 08 Jan 2021 16:34:11 GMThttps://techcommunity.microsoft.com/t5/excel/complex-nesting-formula/m-p/2044058#M85362Myagemtag2021-01-08T16:34:11ZRe: Complex nesting formula
https://techcommunity.microsoft.com/t5/excel/complex-nesting-formula/m-p/2044377#M85375
<P><LI-USER uid="922539"></LI-USER> </P>
<P>I'd create helper range at any place within workbook</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="image.png" style="width: 474px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/244949i178C708DDE45E6D6/image-size/large?v=v2&px=999" role="button" title="image.png" alt="image.png" /></span></P>
<P>With it formula for latest filled month could be</P>
<LI-CODE lang="excel">=LOOKUP(IFNA(INDEX($G$4:$R$4,XMATCH(,$G$4:$R$4)-1),$R$4),$T$4:$T$9,$U$4:$U$9)</LI-CODE>Fri, 08 Jan 2021 18:50:35 GMThttps://techcommunity.microsoft.com/t5/excel/complex-nesting-formula/m-p/2044377#M85375Sergei Baklan2021-01-08T18:50:35ZRe: Complex nesting formula
https://techcommunity.microsoft.com/t5/excel/complex-nesting-formula/m-p/2108469#M87638
Thank you very much this worked perfect!!Fri, 29 Jan 2021 19:19:00 GMThttps://techcommunity.microsoft.com/t5/excel/complex-nesting-formula/m-p/2108469#M87638Myagemtag2021-01-29T19:19:00ZRe: Complex nesting formula
https://techcommunity.microsoft.com/t5/excel/complex-nesting-formula/m-p/2108502#M87647
<P><LI-USER uid="922539"></LI-USER> , you are welcome</P>Fri, 29 Jan 2021 19:54:53 GMThttps://techcommunity.microsoft.com/t5/excel/complex-nesting-formula/m-p/2108502#M87647Sergei Baklan2021-01-29T19:54:53ZRe: Complex nesting formula
https://techcommunity.microsoft.com/t5/excel/complex-nesting-formula/m-p/2108533#M87651
Lol..ok now I have another similar question.<BR />Same type scenario. Jan thru Dec columns, populated with dollar amounts.<BR />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 $.<BR />I have the following & it works for some of the cells.<BR />=if(r25="","",lookup(ifna(index(s25:ac25,xmatch(,s25:ac25)-1),s25:s25),s25:ac25))<BR />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.<BR />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!!Fri, 29 Jan 2021 20:17:43 GMThttps://techcommunity.microsoft.com/t5/excel/complex-nesting-formula/m-p/2108533#M87651Myagemtag2021-01-29T20:17:43ZRe: Complex nesting formula
https://techcommunity.microsoft.com/t5/excel/complex-nesting-formula/m-p/2108575#M87662
<P><LI-USER uid="922539"></LI-USER> </P>
<P>If I understood the logic correctly that's like</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="image.png" style="width: 816px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/250672iDC6DB133CC103841/image-size/large?v=v2&px=999" role="button" title="image.png" alt="image.png" /></span></P>
<P>that could be</P>
<LI-CODE lang="excel">=INDEX(R25:AC25,MAX(1,XMATCH(,R25:AC25)-1))</LI-CODE>
<P>As a comment, that's always better to illustrate the question with sample file</P>Fri, 29 Jan 2021 20:54:06 GMThttps://techcommunity.microsoft.com/t5/excel/complex-nesting-formula/m-p/2108575#M87662Sergei Baklan2021-01-29T20:54:06Z