Reverse Index Match to find row?

dobby1303

New Member
Joined
Feb 10, 2016
Messages
37
Hi all,

I have a grid with grades as the row headers and a series of different tests as the column headers, with the data in the grid as the % boundaries for the tests. I would like to have a lookup for the grade achieved based on the test name and % scored.

I've had a look at a few different things and think a reverse index match is probably the cleanest way to do it and I've found ways to look up the column based on the row header and value, but am struggling with flipping it around.

This is what I started with:
=INDEX(B3:G3,,MATCH(B10,INDEX(B4:G6,MATCH(B9,A4:A6,0),),0))
and this is what I'd changed it to:
=INDEX(A4:A6,MATCH(C10,INDEX(B4:G6,MATCH(C9,B3:G3,0),),0),)

Could someone please explain where I've gone wrong?
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,829
Office Version
365
Platform
Windows
Could you provide the table. Just copy and paste it here.
 

dobby1303

New Member
Joined
Feb 10, 2016
Messages
37
Current LevelSS Biology Paper 1SS Biology Paper 2SS Chemistry Paper 1SS Chemistry Paper 2SS Physics Paper 1SS Physics Paper 2
U0%0%0%0%0%0%
1c10%10%9%9%8%8%
1b14%14%13%13%12%12%
1a18%18%17%17%15%15%
2c23%23%21%21%18%18%
2b27%27%25%25%22%22%
2a31%31%28%28%25%25%
3c35%35%32%32%29%29%
3b39%39%36%36%33%33%
3a43%43%40%40%36%36%
4c48%48%44%44%39%39%
4b51%51%47%47%44%44%
4a55%55%51%51%48%48%
5c58%58%54%54%53%53%
5b73%73%70%70%68%68%
5a87%87%85%85%84%84%

<colgroup><col span="7"></colgroup><tbody>
</tbody>


so the values given are the % on a test (which may not match exactly) and the test sat and I'm trying to find the level
 
Last edited:

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
3,979
Office Version
365, 2019, 2016
Platform
Windows
try this

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Current Level</td><td style=";">Foundation (%)</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style=";">SS Biology Paper 1</td><td style=";">SS Biology Paper 2</td><td style=";">SS Chemistry Paper 1</td><td style=";">SS Chemistry Paper 2</td><td style=";">SS Physics Paper 1</td><td style=";">SS Physics Paper 2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">U</td><td style="text-align: right;;">0%</td><td style="text-align: right;;">0%</td><td style="text-align: right;;">0%</td><td style="text-align: right;;">0%</td><td style="text-align: right;;">0%</td><td style="text-align: right;;">0%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">1c</td><td style="text-align: right;;">10%</td><td style="text-align: right;;">10%</td><td style="text-align: right;;">9%</td><td style="text-align: right;;">9%</td><td style="text-align: right;;">8%</td><td style="text-align: right;;">8%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">1b</td><td style="text-align: right;;">14%</td><td style="text-align: right;;">14%</td><td style="text-align: right;;">13%</td><td style="text-align: right;;">13%</td><td style="text-align: right;;">12%</td><td style="text-align: right;;">12%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">1a</td><td style="text-align: right;;">18%</td><td style="text-align: right;;">18%</td><td style="text-align: right;;">17%</td><td style="text-align: right;;">17%</td><td style="text-align: right;;">15%</td><td style="text-align: right;;">15%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">2c</td><td style="text-align: right;;">23%</td><td style="text-align: right;;">23%</td><td style="text-align: right;;">21%</td><td style="text-align: right;;">21%</td><td style="text-align: right;;">18%</td><td style="text-align: right;;">18%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">2b</td><td style="text-align: right;;">27%</td><td style="text-align: right;;">27%</td><td style="text-align: right;;">25%</td><td style="text-align: right;;">25%</td><td style="text-align: right;;">22%</td><td style="text-align: right;;">22%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">2a</td><td style="text-align: right;;">31%</td><td style="text-align: right;;">31%</td><td style="text-align: right;;">28%</td><td style="text-align: right;;">28%</td><td style="text-align: right;;">25%</td><td style="text-align: right;;">25%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">3c</td><td style="text-align: right;;">35%</td><td style="text-align: right;;">35%</td><td style="text-align: right;;">32%</td><td style="text-align: right;;">32%</td><td style="text-align: right;;">29%</td><td style="text-align: right;;">29%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">3b</td><td style="text-align: right;;">39%</td><td style="text-align: right;;">39%</td><td style="text-align: right;;">36%</td><td style="text-align: right;;">36%</td><td style="text-align: right;;">33%</td><td style="text-align: right;;">33%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">3a</td><td style="text-align: right;;">43%</td><td style="text-align: right;;">43%</td><td style="text-align: right;;">40%</td><td style="text-align: right;;">40%</td><td style="text-align: right;;">36%</td><td style="text-align: right;;">36%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">4c</td><td style="text-align: right;;">48%</td><td style="text-align: right;;">48%</td><td style="text-align: right;;">44%</td><td style="text-align: right;;">44%</td><td style="text-align: right;;">39%</td><td style="text-align: right;;">39%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">4b</td><td style="text-align: right;;">51%</td><td style="text-align: right;;">51%</td><td style="text-align: right;;">47%</td><td style="text-align: right;;">47%</td><td style="text-align: right;;">44%</td><td style="text-align: right;;">44%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">4a</td><td style="text-align: right;;">55%</td><td style="text-align: right;;">55%</td><td style="text-align: right;;">51%</td><td style="text-align: right;;">51%</td><td style="text-align: right;;">48%</td><td style="text-align: right;;">48%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">5c</td><td style="text-align: right;;">58%</td><td style="text-align: right;;">58%</td><td style="text-align: right;;">54%</td><td style="text-align: right;;">54%</td><td style="text-align: right;;">53%</td><td style="text-align: right;;">53%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">5b</td><td style="text-align: right;;">73%</td><td style="text-align: right;;">73%</td><td style="text-align: right;;">70%</td><td style="text-align: right;;">70%</td><td style="text-align: right;;">68%</td><td style="text-align: right;;">68%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">5a</td><td style="text-align: right;;">87%</td><td style="text-align: right;;">87%</td><td style="text-align: right;;">85%</td><td style="text-align: right;;">85%</td><td style="text-align: right;;">84%</td><td style="text-align: right;;">84%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">SS Physics Paper 1</td><td style="text-align: right;;">40%</td><td style="font-style: italic;background-color: #E2EFDA;;">4c</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet5</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C20</th><td style="text-align:left">=INDEX(<font color="Blue">$A$3:$A$18,MATCH(<font color="Red">B20,INDEX(<font color="Green">$B$3:$G$18,,MATCH(<font color="Purple">A20,$B$2:$G$2,0</font>)</font>)</font>),0</font>)</td></tr></tbody></table></td></tr></table><br />
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,829
Office Version
365
Platform
Windows
The table in A1:G17, the exam name in J1 and the % score in J2:

=LOOKUP(J2,INDEX($B$2:$G$17,,MATCH(J1,$B$1:$G$1,0)),$A$2:$A$17)
 

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
5,890
Another option

=INDEX($B$4:$B$19,MATCH(B20,OFFSET(B4,,MATCH($A$20,$C$3:$H$3,0),16),1))
 

Forum statistics

Threads
1,085,726
Messages
5,385,537
Members
401,958
Latest member
rsfalkowski

Some videos you may like

This Week's Hot Topics

Top