# Get first argument for INDEX from calculated column plus one other question

#### Yanta

##### Board Regular
I use a formula such as =IF(ROWS(\$1:1)>COUNT(\$A\$5:\$A\$1840),"",INDEX(Games!E:E,SMALL(\$A\$5:\$A\$1840,ROWS(\$1:1))+4)) to retrieve the value for a given column/row based on the selection from =IF('Analysis Data'!\$GU5>0,ROWS(\$1:1),"")

This compresses a list down to only those that have a non-zero value in the GU column. In this case, called "Technical Fouls".

That's fine when the column is known (E:E in the above example). However, GU contains the game (or column offset), in which the Technical foul was called. For each game I record Court, Referee, referee performance, grade and age group. There may be up to 8 games. Game details start in column I.

So If I called a technical foul in game 5 I'd want to retrieve the values in AC through AG in the Games sheet.

Where I'm stuck in how to modify the first argument of INDEX to pick the correct column in this formula: =IF(ROWS(\$1:1)>COUNT(\$A\$5:\$A\$1840),"",INDEX(Games!E:E,SMALL(\$A\$5:\$A\$1840,ROWS(\$1:1))+4))

The second question is:

On occasion a referee may call more than one technical foul in a session. If the sum of GD:GK is 0 there were no technical fouls. If it is 1 there was 1 technical foul called in a session. It's unlikely that a referee would call technical fouls in more than two games in a session. BUT It is possible that they call 2 or more in a single game.

I've been asked to produce a report showing all technical fouls called by a referee for all games that person has officiated.

The first part of this post gets the first tech foul called for a session, but I also need to get the tech fouls in the same game if there were more than one, and any tech fouls in any subsequent games.

thanks

Last edited:

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### RasGhul

##### Well-known Member
Can you post some sample data?

Replies
0
Views
177
Replies
16
Views
1K
Replies
3
Views
411
Replies
0
Views
254
Replies
6
Views
307

1,130,407
Messages
5,641,968
Members
417,249
Latest member
serrulate

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back