Index & Match Help

[JustTheTim]

Hello, I'm slightly above a novice with Excel. I'm trying to figure out a quicker way of returning values from a master data sheet to another sheet that is showing the individual categories. Master data has approximately 14 teams. Each team is scored on 8 different metric ratings every month of the year. I have 9 total sheets in the workbook: 1 for Master data, the rest are named according to the metric ratings. I have used Define Name for each of the metric ratings as well as teams and month. This was my first attempt:

=INDEX(AHT_Rating,MATCH($B$2,Team,0),MATCH($A3,Month_Value,0))

AHT_Rating, Team, Month_Value are all Defined Names (ranges in a single column). AHT is the metric. B2 contains "Team 11" and A3 contains "1". The response I'm getting is #N/A

Any help would be appreciated. I know I may be way off so feel free to ask clarifying questions.

 

[pjmorris]

really not sure how to fix the problem, but the key will be to identify what is causing the #N/A. To do so either use the 'Evaluate Formula' option on the Formula Ribbon or you can simply press F9 to step through how the formula is evaluated. You will see which component is causing the error. You then work back through that particular element to find out the problem.

Apologies for not providing an answer, but certainly a useful skill to acquire.

 

[sriche01]

The n/a means that one or both of your match statements does not contain the lookup value in the lookup array. It would help if you listed what is in the named ranges. I also agree with PJ that using the evaluate formula tool will help.

 

[JustTheTim]

Thank you both for your responses. The F9 piece i did not know before and will be useful in the future. I think I've figured out the error is because both Match references are columns. What I've learned is that one match should reference a column and one should reference a row. The index then returns where the matches join.

 

[jasonb75]

If you're trying to match 2 rows of data, then this should do it if the AHT_Rating is a numeric value.

=AGGREGATE(15,6,AHT_Rating/(Team=$B$2)/(Month_Value,=$A3),1)

If the AHT_Rating is text, then this should do it.

=INDEX(AHT_Rating,AGGREGATE(15,6,(ROW(AHT_Rating)-ROW(INDEX(AHT_Rating,1))+1)/(Team=$B$2)/(Month_Value,=$A3),1)

For the above to work, the 3 named ranges all need to have the same number of rows. Merged cells will most likely cause problems.