Dependent Index/Match Struggles

[Darko_Giac]

Hi all,

I'm having a bit of an issue with an INDEX/MATCH formula that should be fairly straightforward, however, something doesn't seem to be working.

Essentially, I've made a reference table with a several rows which feeds into a much more concise calculated table (a scoring table).

I've populated the reference table with an age group, and the raw scores and percentiles that correspond to that particular age group (as students in different age brackets will obtain a different percentile based on their raw score).

So in that summary table, I would simply enter the age bracket, the raw score, and that table would return the percentile that corresponds to that raw score/age. However, one of the percentiles is returning as an incorrect value.

I've attached a sample workbook to clarify the issue, any help would greatly be appreciated!

Darko

[protonLeah]

".... one of the percentiles is returning as an incorrect value...." specify.

For the example you give, the raw score is zero. The MATCH(1,(G5=A:A)*(G6=C:C),0)returns a 5 since the first zero is at C5 and the index returns >16 at B5

[Darko_Giac]

Ah I see, thanks for the suggestion! So, I have a few other tables doing this, but none of them seem to have this issue. For this particular example, when the raw score is 0, I would like for it to return the ≤1 value. (However, I don't see the match returning a 5).

But the raw score in the sample workbook is meant to be 0, because that's a possible option. So, the only cell that should be manually changed is G6. G7 gets auto-populated, and G5 remains static.

If, for example, in G6, you enter 10 as the raw score, G7 should populate "2-5".

I hope I'm interpreting your point correctly!

[D13L]

Hi Darko,

Please try:

Please Login or Register  to view this content.

Hope this is what you are looking for.

[Darko_Giac]

Hi D13L, this does return the value I'm looking for, but unfortunately begins to misbehave when I enter other raw scores. I.e., every raw score entered returns a percentile of >16

[D13L]

Sorry about that didn't test it properly.

Please try:

Please Login or Register  to view this content.

Note: This is an array CTRL + SHIFT + ENTER

This one appears to work. I've attached an excel file.

[Darko_Giac]

Perfect! Worked like a charm. Thank you!

[D13L]

Pleasure to help Darko_Giac.

Please close this thread as SOLVE.