How to use Index and Match in a complex table

I have multiple conditions to search for in a certain table:

table1.png

I'm trying to work on this 2 possible conditions:
1. The grade level changes
2. The Reading Speed also changes depending on the student's reading capacity.

For example in A10 (which is constant) I entered "4" as the grade level, therefore in the table I must choose Grade 4.
In D15, the result is =182, therefore I need to find Grade 4 inside the table and search if the value of D15 is found there. Since there are 3 categories in the table which are Fast, Average and Slow, E15 should show what is the category of D15. In this case, it should say "Fast" since 182 is above 140.
If D15 is 126, then E15 should show "Average" since it did not reach the category of 140 above and is also not below 110 which is the limit of the Grade 4 level.

I'm having a problem making a concrete Index-Match formula for this.

Sample File

Can anyone assist me?

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachements to open the upload window.

View Pic

Is it absolutely essential to have the lookup table be in the pictured format? I think it would be a lot easier to organize the lookup table in a different format. I might use something like:

Please Login or Register  to view this content.

It will be important to have the reading speeds be in ascending order, so be sure to fill in the table accordingly. each element of the table will be "slow", "average", or "fast" according to your original table.

Then you only need two MATCH() functions and an INDEX() function to return the desired result. (if you are unfamiliar with these: https://support.office.com/en-us/art...__toc309306714 )

=INDEX($B$2:$G$20,MATCH(A10,$A$2:$A$20,1),MATCH(D15,$B$1:$G$1,1)) Note that we are using 1 as the third argument to the match() functions, which allows us to search for and locate values in between the tabulated values correctly.

Assuming I was allowed to rearrange the table, and I have a hard time imagining a scenario where I would not be able to do so, that's how I would approach something like this.

The -- indicate cells. Reading speeds are in the leftmost column of the lookup table, and the grade levels are at the top of the table.

According to your picture, 70 is the lower limit for "grade 1 fast readers". So, there is a 70 in the 1st column so that the lookup function will be able to find this value.

What pattern are you looking for? I don't think there is any pattern to the data, it is simply entering the lower limit for each category (0 is the assumed lower limit for slow reader at all grade levels).

Because of the way Excel's lookup functions work when the "lookup type" argument is set to 1 or TRUE, there is not need to input "ranges". So, in the example of 182, the MATCH() function will see that 182 fits between 170 and 190, then find the entry under grade 4 for that range, and return the result. If you are unfamiliar with how Excel's lookup functions work in these cases, I would suggest you spend some time with it. Study the help file I linked to above. Work through those examples. Set up your lookup table and work through your own problem. Then ranges are defined by successive entries in the 1st column.

If you are unfamiliar with it, the "evaluate formula tool" may be useful for debugging this: https://support.office.com/en-us/art...6-a70aa409b8a7

Stepping through the function, I see that the 1st MATCH() function is returning 7, which makes sense. 141 is between 139 (7th row) and 169 (8th row), so it is correctly returning 7.
The second MATCH() function is returning 4, which also makes sense. 4 exactly matches the 4 in the lookup range (4th column), and is correctly returning 4. So, the formula in D22 seems to be working exactly as it should. I suspect a problem with the lookup table.

I notice that some of the new entries are the "max" limits for a given range rather than the "min" limits for the range. Why did you switch and put 139 into A8 instead of 140? Since 140 and above and grade 4 should be "fast", why is D8 "average"? In addition, where is the 120 entry that will divide the grade 3 average from grade 3 fast?