Locate the value relevant to its index value in a excel sheet

[raja1964]

Dear Members,

I am compiling a statistics relating to evaluation of Schools based on various Tests (each on a maximum of 200) on a quarterly basis, the total score relating to each School was exhibited in “column H” of the table I.
The highest and lowest marks (max/min) so far recorded by the Schools during any period was also exhibited in the table I.
The max and min marks obtained by the Schools are further distributed into various levels from 0 to 100 for comparison.
The index value for the current performance (marks) obtained by the Schools are worked out automatically by a formula pasted in "column S" of the table. (answer table).
I enclose herewith a model excel table for better understanding.

Now on the reverse, I want to locate the marks relating to every School relevant to its index value (in col S), to be identified from "columns L to R" and exhibited as in "column T" of table II.
Please help me with a formula for the above.
With regards,
Raja

[Tsjallie]

The formula you need for column U in Table II(answer table) is

Please Login or Register  to view this content.

But one problem remains with School 3:
For this school you're searching the next smaller value compared to 465.
However, such a value is not available in the matrix $L4:$T4 and the result is an error #N/A.
Either the data for this school is incorrect (which I suspect) or you need to extend the matrix to search (L:T) with an extra column having the value 0.
But that still wouldn't give a result of 580. That would be 0 instead, which is the next smaller value.

[raja1964]

The formula you need for column U in Table II(answer table) is

Please Login or Register  to view this content.

But one problem remains with School 3:
For this school you're searching the next smaller value compared to 465.
However, such a value is not available in the matrix $L4:$T4 and the result is an error #N/A.
Either the data for this school is incorrect (which I suspect) or you need to extend the matrix to search (L:T) with an extra column having the value 0.
But that still wouldn't give a result of 580. That would be 0 instead, which is the next smaller value.

Thank you Tsjallie,
I sincerely appreciate your help.
With your permission, I would kindly prefer to correct some of the errors (pointed by you) in the matrix table and to elaborate a few facts:-
1) The matrix in the problem is between L1 to R6 only and not between L1 to S6 (as stated in reply).
2) Further, the match has to be for the range between S2 to S6 only and not for T2 to T6.
3) The required result is to be as shown in answer table “column T". (Expected answer is narrated for easy understanding in column T).
4) The following Error is rectified: The maximum marks for each school shall be read as 250 instead of 200 as typed in the original table.
5) The values for school 3 has been revised as pointed out by the member to make the value to fall within the range available inside the matrix.
6) The values for school 5 have also been revised to hold the values (test marks) within the maximum of 250 fixed for any test.
7) The revised values for school 3 and 5 are shown in blue colored boxes for easy reference.
In the solution given by you, I revised the range in the formula to suit the existing cell range as under:
And the revised formula should be read as " =INDEX($L$1:$R$6,ROW(),MATCH($S2,$L$1:$R$1,0)) ".
The formula works.
I have applied the formula given by Tsjallie, (as revised) in the newly added column “U” of the answer table to show the results.
With Regards,
Raja
P.S. - I have enclosed herewith a “modified excel data sheet2 dt 25.11.16” table with corrections for reference.
Sir,
Can I ask you one more help as an additional requirement over the solution provided by you?
Kindly refer “modified excel data sheet2 dt 25.11.16” enclosed above.
I want a formula to extract the higher and lower cell values available in the cell adjacent to the answer cell (preceding/succeeding cells to the answer cell in column “U” of table II) to appear in “columns V & W” as illustrated below (also illustrated in table II):

Reference to Row Answer value (as in column “U”) Adjacent cell value (Lower value) Adjacent cell value (higher value)
I-(school1) 696 615 858
II-(school2) 772 626 845
III-(school3) 580 500 660
IV-(school4) 360 N/A 483
V-(school5) 1100 944 N/A

I Thank you Tsjallie, Once again, for your early help,
Raja

[Tsjallie]

Glad I could help.
Thx for the feedback and rep

Don't forget to mark your thread "Solved".

[raja1964]

The formula you need for column U in Table II(answer table) is

Please Login or Register  to view this content.

But one problem remains with School 3:
For this school you're searching the next smaller value compared to 465.
However, such a value is not available in the matrix $L4:$T4 and the result is an error #N/A.
Either the data for this school is incorrect (which I suspect) or you need to extend the matrix to search (L:T) with an extra column having the value 0.
But that still wouldn't give a result of 580. That would be 0 instead, which is the next smaller value.

Thank you Tsjallie,
I sincerely appreciate your help.
With your permission, I would kindly prefer to correct some of the errors (pointed by you) in the matrix table and to elaborate a few facts:-
1) The matrix in the problem is between L1 to R6 only and not between L1 to S6 (as stated in reply).
2) Further, the match has to be for the range between S2 to S6 only and not for T2 to T6.
3) The required result is to be as shown in answer table “column T". (Expected answer is narrated for easy understanding in column T).
4) The following Error is rectified: The maximum marks for each school shall be read as 250 instead of 200 as typed in the original table.
5) The values for school 3 has been revised as pointed out by the member to make the value to fall within the range available inside the matrix.
6) The values for school 5 have also been revised to hold the values (test marks) within the maximum of 250 fixed for any test.
7) The revised values for school 3 and 5 are shown in blue colored boxes for easy reference.
In the solution given by you, I revised the range in the formula to suit the existing cell range as under:
And the revised formula should be read as " =INDEX($L$1:$R$6,ROW(),MATCH($S2,$L$1:$R$1,0)) ".
The formula works.
I have applied the formula given by Tsjallie, (as revised) in the newly added column “U” of the answer table to show the results.
With Regards,
Raja
P.S. - I have enclosed herewith a “modified excel data sheet2 dt 25.11.16” table with corrections for reference.
Sir,
Can I ask you one more help as an additional requirement over the solution provided by you?
Kindly refer “modified excel data sheet2 dt 25.11.16” enclosed above.
I want a formula to extract the higher and lower cell values available in the cell adjacent to the answer cell (preceding/succeeding cells to the answer cell in column “U” of table II) to appear in “columns V & W” as illustrated below (also illustrated in table II):

Reference to Row Answer value (as in column “U”) Adjacent cell value (Lower value) Adjacent cell value (higher value)
I-(school1) 696 615 858
II-(school2) 772 626 845
III-(school3) 580 500 660
IV-(school4) 360 N/A 483
V-(school5) 1100 944 N/A

I Thank you Tsjallie, Once again, for your early help,
Raja

[raja1964]

I have enclosed herewith a “modified excel data sheet2 dt 25.11.16” table with corrections for reference.

[Tsjallie]

When row = 2
Formula for column V would be:

Please Login or Register  to view this content.

Formula for column W would be:

Please Login or Register  to view this content.

I think the formulas are pretty easy to follow, but if you need any explanation feel free to ask.

[raja1964]

When row = 2
Formula for column V would be:

Please Login or Register  to view this content.

Formula for column W would be:

Please Login or Register  to view this content.

I think the formulas are pretty easy to follow, but if you need any explanation feel free to ask.

The formulas work.
As you said, the formulas are pretty easy to follow. I am satisfied.
Thanks to you sir,
raja