How to have the formula add or subtract 1 year in a Index -Aray formula

[Meshell]

Hi there,

I created this formula a few years back, and I'm trying to update it. The last section that ends with it looking at $C$3 is the area I want to update. I want it to be able to Add 1 or Subtract 1 to the Year it is looking up/referencing.

Currently I've created another cell with the different years, but this gets old when you have to change the formula at the new year. Any ideas would be greatly appreciated.

=INDEX('Purcap Storage'!$H$3:$H$10000,MATCH(1,('Purcap Storage'!$E$3:$E$10000='Store SWAS'!$A$3)*('Purcap Storage'!$D$3:$D$10000='Store SWAS'!$E$3)*('Purcap Storage'!$B$3:$B$10000='Store SWAS'!$M$29)*('Purcap Storage'!$A$3:$A$10000='Store SWAS'!$C$3),0))

Thanks for your time.

[protonLeah]

What's in 'Store SWAS'!$C$3?

[Meshell]

In that field is the year 2016 currently. I want the formula to add or subtract 1 year from it though.

[protonLeah]

=YEAR(NOW()) in C3 will always have the current year.

[Meshell]

Thats awesome! I will use that code. I guess I should be more specific to what I was trying for though. The formula I use looks up information from 1 sheet to another from Feb - Jan is the year cycle. So my problem is in the Jan formula area I have to have a different year cell in order for it to work.

This is why I was trying to find out how to tell the formula that I want it to either add a year or subtract a year, depending on it's location.

For example Feb-Dec 2016 but when it gets to Jan the formula needs to read for 2017 and they are part of the same "year" at work.

Hope that helps? So I'm wanting to eliminate trying to use a different cell and just use the one cell. Is there a way to do that? Thanks in advance!!

[protonLeah]

(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

• Click Advanced next to Quick Post button at the bottom right of the editor box.
• Scroll down until you see "Manage Attachments",
• Click the "Choose" button at the upper left (upload from your computer).
• Select your file, click "open", click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Close the Attachment Manager window.
• Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!