INDEX/MATCH Intermitent error

[Ivan1297]

Good Morning!

Hoping someone can help me with this question, I am using Index/match to create a dashboard for a set of data with multiple headings, and across 7 years I am ussing the following formula

=INDEX('Dashboard Data'!B23:AG34,MATCH(selected_month,month),MATCH(selected_year,'Dashboard Data'!$B$21:$AG$21,0))

the formula works and it pulls in the data for the specified month and year however I was checking for errors and it does not work for when i select Feb, April, Aug or Dec. the data is there and it pulls it for every other month except those 4. I have checked for spacing and formatting and nothing seems to work and the formula is correct as it pulls the data correctly for everything else.

maybe someone has run into this before or can point me to the right solution.

 

[Cubist]

Can you show the named ranges you're looking up from?

 

[StephenCrump]

@Ivan1297, welcome to the Forum!

Based on just a quick look, if you're copying this formula down to other rows, you'll need to lock these row references:

=INDEX('Dashboard Data'!B$23:AG$34 ...

Also selected_month and selected_year presumably refer to January 2016? If so, you'll need to change these references for other months/years.

=INDEX('Dashboard Data'!B23:AG34,MATCH(selected_month,month),MATCH(selected_year,'Dashboard Data'!$B$21:$AG$21,0))

 

[Ivan1297]

Can you show the named ranges you're looking up from?

the Name ranges i am looking for are

Inbound Calls

Average Speed of Answer in Secs

Abandoned Calls

Call Abandonment Rate

and i am indexing them based on month and year

	2016
Month	Inbound Calls	Average Speed of Answer in Secs	Abandoned Calls	Average of Call Abandonment Rate
January	3,171​	25	45	1%​
February	2,520​	18	30	1%​
March	4,379​	29	72	2%​
April	6,009​	29	110	2%​
May	3,559​	49	130	4%​
June	3,790​	62	164	4%​
July	3,685​	43	82	2%​
August	3,472​	44	100	3%​
September	3,101​	29	62	2%​
October	2,752​	18	40	1%​
November	2,763​	21	45	2%​
December	2,584​	22	37	1%​
Total	41,785	389	917	2%​

 

[Ivan1297]

I figured it out i was missing a 0 after the first match query) formula works now thanks everyone

=INDEX('Dashboard Data'!B$3:AG$14,MATCH(selected_month,month,0),MATCH(selected_year,'Dashboard Data'!$B$1:$AG$1,0))