Help with INDEX, MATCH from 3 criteria

**JO505** · 12-13-2011, 04:56 PM

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

**NBVC** · 12-13-2011, 05:16 PM

Try:

Please Login or Register  to view this content.

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

**Haseeb Avarakkan** · 12-13-2011, 05:26 PM

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.

**NBVC** · 12-13-2011, 05:41 PM

Definitely a more concise option

**JO505** · 12-13-2011, 06:59 PM

Haseeb,

I that works nery well but I do have a question. How does the +COLUMNS($K6:K6)-1,0) function?

Jim O

**Haseeb Avarakkan** · 12-13-2011, 07:20 PM

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

**JO505** · 12-13-2011, 07:59 PM

I thank you both for your help and time with this problem. It may take some time to fully understand what is going on with the formula but for now it works.

Thanks Again!

Jim O