If or Index/Match that updates based on date?

[IAmLemondrop]

Hello. I’m using Excel 2010 and trying to automate a report I have to update every month. I’ve used a named range (CurrentMonth) for the reporting month and an index for the monthly data I need to update. This works fine for timephased data, however, there is one column that needs to get info from the latest sheet based on the date. I always use a M/1/YYYY for the date, so I used Excel’s serial numbers for the dates (e.g. 41275=1/1/2013, 41306=2/1/2013, etc).
</SPAN>
So my current formula is:</SPAN>
=IF(CurrentMonth=41275,INDEX('Info-01'!B2:B4,MATCH(Sheet1!A2,'Info-01'!A2:A4,0),1),</SPAN>
IF(CurrentMonth=41306,INDEX('Info-02'!B2:B4,MATCH(Sheet1!A2,'Info-02'!A2:A4,0),1),</SPAN>
IF(CurrentMonth=41334,INDEX('Info-03'!B2:B4,MATCH(Sheet1!A2,'Info-03'!A2:A4,0),1))))
</SPAN>
I’ll have to expand it to include dates for the entire year, but my question is really, isn’t there a better way to go about this?</SPAN>

 

[Jerry Sullivan]

Hi and Welcome to the Board,

You could use the INDIRECT worksheet function to simplify your formula.
=INDEX(INDIRECT("'Info-0" & MONTH(CurrentMonth) & "'!B2:B4"),MATCH(A2,INDIRECT("'Info-0" & MONTH(CurrentMonth) & "'!A2:A4"),0))

If you are only looking up one field on the Matching row, then Vlookup is probably a better choice...
=VLOOKUP(A2,INDIRECT("'Info-0" & MONTH(CurrentMonth) & "'!A2:B4"),2,0)

 

[IAmLemondrop]

I've guess I need to do some reading up on the indirect function (its not one I've used before). That worked like a charm. Thank you!

 

[Jerry Sullivan]

You're very welcome.

Indirect has some undersirable qualities such as being Volatile (recalculating when anything is calculated) and not working for links to external workbooks that are not open. So Indirect should be avoided if possible.

Nonetheless, in some cases like this there might not be a better option so the benefits of Indirect outweigh the drawbacks.