# Nested If statement to display latest month's values from YTD section

#### Teejicus

##### New Member
I'm sorting out my supplier KPI documents for 2016 and currently on their overview tab they have a YTD section showing their monthly scores for each metric of the year so far, and on the right of that is a "Current Month" section that just shows the latest month's scores. (for reasons that are too long to go into I need both sections even though the current month side is redudant)

At the minute they have to manually copy and paste the latest scores from the YTD section into the Current Month section. I thought I'd be clever and try to automate that bit for them with a nested if statement in the current month side that would pick up the latest score and display it - but my wits seem to have abandoned me and all I'm getting is a blank cell.

I've attached a rough example of what I'm trying to do - has anyone got any ideas?

#### Attachments

• 9.9 KB Views: 14

#### chirayu

##### Well-Known Member
Always looks at previous month. Filled with sample data to show.

#### Attachments

• 13.2 KB Views: 17

#### Teejicus

##### New Member
Brilliant! I modified it slightly so that instead of looking for a match to current month - 1, it's now referencing a drop down box / list of months so that I can select whichever month I want to view. Thank you so much!

#### chirayu

##### Well-Known Member
Here's a sample with month selection using Vlookup match

#### Attachments

• 13.4 KB Views: 26

#### Khalid NGO

##### Excel Ninja
Hi all,
Another option based on last attachment:

=SUMIF(\$B\$2:\$M\$2,\$P\$1,\$B3:\$M3)

=LOOKUP(--(1&\$P\$1),--(1&\$B\$2:\$M\$2),\$B3:\$M3)

Both are regular formula, and are not dependent on Metric*

Regards,

#### klimbo13

##### New Member
I created a Line and Clustered Column Chart to display sales from 2011 to 2016. I want to display ALL sales of current year and sales of previous year from 2011 to 2015. However, I don't have intact sales of 2016. Therefore, I want to compare sales YTD of 2016 and sales YTD of 2015 comparing 2016 in the same chart. It displays sale of 2015 and sales YTD of 2016 now. I contacted technical support. He told me that I can use forecasting. I don't think forecasting would help. Anyone can help? Plex Kodi Lucky Patcher

#### chirayu

##### Well-Known Member
@klimbo13 please start new thread with sample data and sample output attached

Him

My try

#### Attachments

• 9.9 KB Views: 6