Index match formula

[Buffalo2018]

I am trying to automate my financials (multiple tabs) using a drop down for dates and an index match formula. I cannot seem to get my formula to work referencing the date cell through the tabs unless I manually put the date in the formula. See below in bold.

=INDEX('GL Trend'!B535:G569,MATCH(A12,'GL Trend'!A535:A569,0),MATCH("5/1/2019",'GL Trend'!B533:G533,0))

Note: I am referencing the cell for PL25 and PL30 due to multiple entities in the GL data. So another company would have PL26 for Sales and PL31 for COGS.

A sample of my spreadsheet below:

		Actuals		Budget
		May-19		May-19
PL25	Sales	xxx		xxx
PL30	COGS	xxx		xxx

<tbody>
</tbody>

I'd appreciate any help!

 

[Domenic]

Re: Help with index match formula

It looks like the dates in B533:G533 are true date values, in which case you'll need to coerce your string formatted date into a true date value...

[B]"5/1/2019"+0[/B]

Hope this helps!

 

[Buffalo2018]

Re: Help with index match formula

I have tried that and it didn't make a difference when I changed the formula to the referenced date cell...

 

[Domenic]

Re: Help with index match formula

Can you post the exact formula that returned an error?

 

[Buffalo2018]

Re: Help with index match formula

=INDEX('AS GL Trend'!B535:N569,MATCH($A12,'AS GL Trend'!A535:A569,0),MATCH("5/1/2019",'AS GL Trend'!B533:N533,0))

I've made sure that the date format is the same throughout...

 

[Domenic]

Re: Help with index match formula

You haven't added the +0 to your string formatted date. Your formula should be...

[COLOR=#333333]=INDEX('AS GL Trend'!B535:N569,MATCH($A12,'AS GL Trend'!A535:A569,0),MATCH("5/1/2019"[/COLOR][COLOR=#ff0000]+0[/COLOR][COLOR=#333333],'AS GL Trend'!B533:N533,0))[/COLOR]

 

[Buffalo2018]

Re: Help with index match formula

Hi Domenic - Thank you. That does work, but I want to make sure that when I use my date drop down box from my main tab (which will convert the date for all tabs) it will change the data for the corresponding date throughout the worksheet. So I'd prefer to reference the date cell rather than use "5/1/2019" in the formula. I want to make this as automatic as possible. Any suggestions? When I do reference the cell it will work for May, but if I change the date (in the drop down) to April I get an #N/A.

 

[Domenic]

Re: Help with index match formula

It looks like the source data contains dates specified as the first of the month. If so, your lookup value needs to be a date that represents the first day of the specified month. However, if the lookup value is a date other than the first of the month, but you want only want to match the month, the formula will need to be amended accordingly.

When you change the date in your cell to April, what is the actual value? Is it the text value April? Is it the first day of April (ie. April 1, 2019 or 1-Apr-19 or Apr-19)? Or is it some other date within the month (ie. April 12, 2019)?

 

[Buffalo2018]

Re: Help with index match formula

Got it! Thank you for your help!