Thread: Help with INDEX, MATCH from 3 criteria

Trying to do a football sheet to compair team scoring by quarter. In the attached file I want to return the scores by team, week and quarter from the sample sheet to the results sheet.

Any help would be appreciated.

Jim O

Try:

=INDEX(INDEX('Data Sample'!$E$3:$AB$3,MATCH(Result!$I$4,'Data Sample'!$E$1:$AB$1,0)):'Data Sample'!$AB$34,MATCH(Result!$I6,'Data Sample'!$D$3:$D$34,0),COLUMNS($K$2:K$2))

If you remove blank row between team names in column I, then you can easily copy formula down.

Try this;

=VLOOKUP($I6,'Data Sample'!$D:$AB,MATCH($I$4,'Data Sample'!$D$1:$AB$1,0)+COLUMNS($K6:K6)-1,0)

copy down.

Definitely a more concise option

COLUMNS($K6:K6). COLUMNS will count how many column between ranges. here is 1, K:K, which is column K. We have stopped starting cell with $ which is $K6. When drag across will give 2,3,4,5,.....

MATCH($I$4,'Data Sample'!$D$1:$AB$1,0) will give you the position where Week starts. In the same column where Week starts also starts number 1, then next column 2, then 3 etc... So if you use COLUMNS($K6:K6)-1 will give 0 which will minus add to MATCH. When copy across this will add +1 column to MATCH, so VLOOKUP will give the result from columns.

Hope this helps