Trouble with Index-Match getting #REF!

[KellyAustralia]

Hi guys,
Having trouble with a formula. I have attached my work sample and in "sheet 1" I have highlighted the errors in red. I believe my error has something to do with the length of the first equation being only 6 samples long as the following columns data shows only 6 answers before the #REF! shows.
My formula I am using is
=INDEX((INDEX(Sheet2!$P$1:Sheet2!$P$300,Sheet2!$U$1):INDEX(Sheet2!$P$1:Sheet2!$P$300,Sheet2!$U$2)),MATCH(A2,Sheet2!$O$1:Sheet2!$O$300,0))
which is the equation for cell B2, then copied down.
For cell D2,the equation is
=INDEX((INDEX(Sheet2!$P$1:Sheet2!$P$300,Sheet2!$U$2):INDEX(Sheet2!$P$1:Sheet2!$P$300,Sheet2!$U$3)),MATCH(C2,Sheet2!$O$1:Sheet2!$O$300,0))
, then copied down.
Data is exported from sheet 2.
Thanks for all help.

[:) Sixthsense :)]

Please explain what you are trying to do with this part of the formula?

(INDEX(Sheet2!$P$1:Sheet2!$P$300,Sheet2!$U$2):INDEX(Sheet2!$P$1:Sheet2!$P$300,Sheet2!$U$3))

[KellyAustralia]

Hi Sixthsense,
(INDEX(Sheet2!$P$1:Sheet2!$P$300,Sheet2!$U$2):INDEX(Sheet2!$P$1:Sheet2!$P$300,Sheet2!$U$3)), this part of the formula I have used to gather the names for race 2 which should be between rows 11 and rows 26 which I have calculated from sheet 2.
Likewise, for race 3, I have used -
(INDEX(Sheet2!$P$1:Sheet2!$P$300,Sheet2!$U$3):INDEX(Sheet2!$P$1:Sheet2!$P$300,Sheet2!$U$4)) which should gather the information from between rows 26 and 36.
Thanks.

[:) Sixthsense :)]

Thanks for the brief

The attached screenshot of the evaluated formula will tell you what you are doing wrong

26-11= 15 rows in Index Array
Match is resulting 20

Index only have 15 rows so it is resulting error when we try to pull the 20th row from it

[KellyAustralia]

Hi Sixthsense,
I am so lost by what you mean Match is trying to pull the 20th row. Shouldn't match only be looking to match cell "C2" on sheet 1 with cell "O14" on sheet 2 with the range only between rows 11 and 26 when trying to match the answer for cell "D2" on sheet 1.
How can I change my formula so that I will not get any #REF!
Thanks for your help.

[:) Sixthsense :)]

Just use the match function alone in a cell and check what result it is arriving

[alvin-chung]

Hi Kelly, try look at your formula in D8 below in which you've use the correct range for Sheet2!P11:P26 (green italic) but incorrect range Sheet2!O1:O300 (red italic), that's why you're getting error as Sixthsense highlighted.

Please Login or Register  to view this content.

Tweaking your formula D8 to this shall solve the issue:

Please Login or Register  to view this content.

Having to say that, you can also try a more robust formula which you can copy to any of the race without having to modify for each column (i.e. all formulas in B2:B17, D2:D17, F2:F17 etc are the same using formula below)

Please Login or Register  to view this content.

_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[KellyAustralia]

Thanks Sixthsense and Alvin-Chung for all your help.
The Index-Indirect formula Alvin is great. It works a treat.
Thanks once again.
Kelly

[alvin-chung]

Thank you for the feedback, you're welcome



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin