A puzzling issue in an Index and Match formula

[markaval]

Hi forum,

I've been working on a dashboard to plot Tour De France results, and in the main it's gone ok. I've been working on presenting team stage winners in graphical form displaying the team shirt using INDIRECT, and that's all gone swimmingly. Then I thought I'd add a simple text box above each team shirt detailing the team name - and that's where I've run into a bit of a puzzle.

Here is the formula I've used:

=INDEX(Table_Res_Stage_Team[team],MATCH(ROWS($B$24:B24),Table_Res_Stage_Team[rank],0),MATCH($N$6,Table_Res_Stage_Team[Stage],0))

I've got a table that displays the stage team results and it shows stage, rank for the team, team name, time etc. I want the formula to check the first three placings (1st, 2nd and 3rd) for each stage and to return the team name back to the text box - the rows function links to a feeder column in my analysis sheet that basically checks for 1,2 and 3 in the three populated rows and returns the values associated with the position. The stage number is linked to a combo box on the main dashboard, and that all works perfectly.

The above formula works perfectly for stage 1, and when I copy it down two more rows it returns the right results. However when I click the combo box over to stage 2 I get ref errors linked the three rows that were just working perfectly. When I evaluated each part of the formula, the second MATCH function bizarrely shows the result 23 instead of 2, which is the value linked to $N$6 in the Combo Output cell. Now as I understand it, the whole point of the match type 0 is to perform an exact match and to return the first instance of that value, so since the value in $N$6 is 2 I expected this to work no problem - if so, then why is the formula returning a value of 23 (there isn't even a 23 in the [Stage] column!)? I've racked my brain and I can't think of what I'm doing wrong - does anyone have ideas please?

Many thanks in advance for help with this

Mark

 

[markaval]

Just to let the forum know, I worked this one out for myself. The answer came quickly once I realised that I needed to create a unique reference by concatenating two columns, one of which had a repeating value. I then just made the output an array. This was the final solution:

{=INDEX(Table_Res_Stage_Team[team],MATCH($N$6&ROWS($B$24:B24),Table_Res_Stage_Team[Stage]&Table_Res_Stage_Team[rank],0))}

I was on the right lines with my original approach but the row value being returned for the stage value needed an additional element, and the rank value provided it.