MATCH issue

[ollyhughes1982]

Hi. I have an issue with cell DU5. All other cells work properly except this one. You can see that I am trying to return the best result out of 10 possible ones. It seems to work for the other 2 columns next to it, but for this cell it is returning 6.00%, when it should be returning 8.00%. I don't understand how the two columns next to it can be returning correctly when that one isn't. Seems strange.

Please help! Thanks.

Link to file is below:

https://1drv.ms/x/s!AtIvkRgReIvwgQW4zeMTid-oNo3b

 

[Ramballah]

How do you know it should be 8%?
*EDIT* Nvm, just saw the best time.

 

[ollyhughes1982]

Because the percentage that relates to the best time result is 8.00%. The match is basically looking for the fastest time out of the 10 and then copying the adjacent columns

 

[Ramballah]

How do you know it should be 8%?
*EDIT* Nvm, just saw the best time.

In column X there is also a record with the time of 00:00:03, the index formula is taking the first it can find aka the first 00:00:03 with the age grade of 6.00%. thats why its showing 6% instead of 8% because the 8% record comes in a later column.

 

[ollyhughes1982]

Ah, ok. So how do I adjust it to only look at cells CF5:DS5 for results? I would also only want the other event (event 4) that is also looking at sub-events for the best time, to do this. i.e. Between P5:AU5. Thanks

 

[Ramballah]

Ah, ok. So how do I adjust it to only look at cells CF5:DS5 for results? I would also only want the other event (event 4) that is also looking at sub-events for the best time, to do this. i.e. Between P5:AU5. Thanks

in DU5 put this: =INDEX($CF5:$DS5;MATCH($DT5;$CF5:$BVI5;0)+COLUMN(A1)) all i changed is A5 to CF5. It seems for AW5 (event 4) you should change the A5 to P5. I think this solves your problems?

 

[ollyhughes1982]

[FONT=&quot]So, basically what I want to do is:[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]For Event 4 (Has 8 sub-events, from which I want to identify the fastest time result)[/FONT]
[FONT=&quot]
[/FONT]

• For cell AV5: Find the ‘Time’ column with the lowest time, out of the 8 possible sub-events. i.e. From columns P5,T5,X5,AB5,AF5,AJ5,AN5,AR5. In this case P5 (00:00:01)
• For cell AW5: I then want the corresponding next column value (‘Age-Grade’). In this case Q5 (8.00%)
• For cell AX5: I then want the corresponding next column value (‘Finishing Position’). In this case R5 (1)
• For cell AY5: I then want the corresponding next column value (‘Points League Event Points’). In this case S5 (150)

[FONT=&quot]
[/FONT]
[FONT=&quot]For Event 13 (Has 10 sub-events, from which I want to identify the fastest time result)[/FONT]
[FONT=&quot]
[/FONT]

• For cell DT5: Find the ‘Time’ column with the lowest time, out of the 10 possible sub-events. i.e. From columns CF5,CJ5,CN5,CR5,CV5,CZ5,DD5,DH5,DL5,DP5. In this case CN5 (00:00:03)
• For cell DU5: I then want the corresponding next column value (‘Age-Grade’). In this case CO5 (8.00%)
• For cell DV5: I then want the corresponding next column value (‘Finishing Position’). In this case CP5 (3)
• For cell Dw5: I then want the corresponding next column value (‘Points League Event Points’). In this case CQ5 (148)

[FONT=&quot]
[/FONT]
[FONT=&quot]Any help much appreciated![/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Thanks.[/FONT]

 

[ollyhughes1982]

Thanks. I will try!

 

[Ramballah]

So, basically what I want to do is:


For Event 4 (Has 8 sub-events, from which I want to identify the fastest time result)

• For cell AV5: Find the ‘Time’ column with the lowest time, out of the 8 possible sub-events. i.e. From columns P5,T5,X5,AB5,AF5,AJ5,AN5,AR5. In this case P5 (00:00:01)
• For cell AW5: I then want the corresponding next column value (‘Age-Grade’). In this case Q5 (8.00%)
• For cell AX5: I then want the corresponding next column value (‘Finishing Position’). In this case R5 (1)
• For cell AY5: I then want the corresponding next column value (‘Points League Event Points’). In this case S5 (150)

For Event 13 (Has 10 sub-events, from which I want to identify the fastest time result)

• For cell DT5: Find the ‘Time’ column with the lowest time, out of the 10 possible sub-events. i.e. From columns CF5,CJ5,CN5,CR5,CV5,CZ5,DD5,DH5,DL5,DP5. In this case CN5 (00:00:03)
• For cell DU5: I then want the corresponding next column value (‘Age-Grade’). In this case CO5 (8.00%)
• For cell DV5: I then want the corresponding next column value (‘Finishing Position’). In this case CP5 (3)
• For cell Dw5: I then want the corresponding next column value (‘Points League Event Points’). In this case CQ5 (148)

Any help much appreciated!


Thanks.

Yes so i basically already answered it.
So the ONLY cell you need to change = in DU5 put this: =INDEX($CF5:$DS5;MATCH($DT5;$CF5:$BVI5;0)+COLUMN(A1))
For Event 4 it already is correct. only Event 13 how it is now looks for all the events instead of the 10. So if you just put that in there it will only look for the 10 events. For Event 4 it already looks only at the 8 events. This i already posted.