Reverse Index Match to find row?

[dobby1303]

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]

Could you provide the table. Just copy and paste it here.

 

[dobby1303]

[TABLE="width: 441"]
<colgroup><col span="7"></colgroup><tbody>[TR]
[TD]Current Level[/TD]
[TD="align: right"]SS Biology Paper 1[/TD]
[TD="align: right"]SS Biology Paper 2[/TD]
[TD="align: right"]SS Chemistry Paper 1[/TD]
[TD="align: right"]SS Chemistry Paper 2[/TD]
[TD="align: right"]SS Physics Paper 1[/TD]
[TD="align: right"]SS Physics Paper 2[/TD]
[/TR]
[TR]
[TD]U[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]0%[/TD]
[/TR]
[TR]
[TD]1c[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]9%[/TD]
[TD="align: right"]9%[/TD]
[TD="align: right"]8%[/TD]
[TD="align: right"]8%[/TD]
[/TR]
[TR]
[TD]1b[/TD]
[TD="align: right"]14%[/TD]
[TD="align: right"]14%[/TD]
[TD="align: right"]13%[/TD]
[TD="align: right"]13%[/TD]
[TD="align: right"]12%[/TD]
[TD="align: right"]12%[/TD]
[/TR]
[TR]
[TD]1a[/TD]
[TD="align: right"]18%[/TD]
[TD="align: right"]18%[/TD]
[TD="align: right"]17%[/TD]
[TD="align: right"]17%[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]15%[/TD]
[/TR]
[TR]
[TD]2c[/TD]
[TD="align: right"]23%[/TD]
[TD="align: right"]23%[/TD]
[TD="align: right"]21%[/TD]
[TD="align: right"]21%[/TD]
[TD="align: right"]18%[/TD]
[TD="align: right"]18%[/TD]
[/TR]
[TR]
[TD]2b[/TD]
[TD="align: right"]27%[/TD]
[TD="align: right"]27%[/TD]
[TD="align: right"]25%[/TD]
[TD="align: right"]25%[/TD]
[TD="align: right"]22%[/TD]
[TD="align: right"]22%[/TD]
[/TR]
[TR]
[TD]2a[/TD]
[TD="align: right"]31%[/TD]
[TD="align: right"]31%[/TD]
[TD="align: right"]28%[/TD]
[TD="align: right"]28%[/TD]
[TD="align: right"]25%[/TD]
[TD="align: right"]25%[/TD]
[/TR]
[TR]
[TD]3c[/TD]
[TD="align: right"]35%[/TD]
[TD="align: right"]35%[/TD]
[TD="align: right"]32%[/TD]
[TD="align: right"]32%[/TD]
[TD="align: right"]29%[/TD]
[TD="align: right"]29%[/TD]
[/TR]
[TR]
[TD]3b[/TD]
[TD="align: right"]39%[/TD]
[TD="align: right"]39%[/TD]
[TD="align: right"]36%[/TD]
[TD="align: right"]36%[/TD]
[TD="align: right"]33%[/TD]
[TD="align: right"]33%[/TD]
[/TR]
[TR]
[TD]3a[/TD]
[TD="align: right"]43%[/TD]
[TD="align: right"]43%[/TD]
[TD="align: right"]40%[/TD]
[TD="align: right"]40%[/TD]
[TD="align: right"]36%[/TD]
[TD="align: right"]36%[/TD]
[/TR]
[TR]
[TD]4c[/TD]
[TD="align: right"]48%[/TD]
[TD="align: right"]48%[/TD]
[TD="align: right"]44%[/TD]
[TD="align: right"]44%[/TD]
[TD="align: right"]39%[/TD]
[TD="align: right"]39%[/TD]
[/TR]
[TR]
[TD]4b[/TD]
[TD="align: right"]51%[/TD]
[TD="align: right"]51%[/TD]
[TD="align: right"]47%[/TD]
[TD="align: right"]47%[/TD]
[TD="align: right"]44%[/TD]
[TD="align: right"]44%[/TD]
[/TR]
[TR]
[TD]4a[/TD]
[TD="align: right"]55%[/TD]
[TD="align: right"]55%[/TD]
[TD="align: right"]51%[/TD]
[TD="align: right"]51%[/TD]
[TD="align: right"]48%[/TD]
[TD="align: right"]48%[/TD]
[/TR]
[TR]
[TD]5c[/TD]
[TD="align: right"]58%[/TD]
[TD="align: right"]58%[/TD]
[TD="align: right"]54%[/TD]
[TD="align: right"]54%[/TD]
[TD="align: right"]53%[/TD]
[TD="align: right"]53%[/TD]
[/TR]
[TR]
[TD]5b[/TD]
[TD="align: right"]73%[/TD]
[TD="align: right"]73%[/TD]
[TD="align: right"]70%[/TD]
[TD="align: right"]70%[/TD]
[TD="align: right"]68%[/TD]
[TD="align: right"]68%[/TD]
[/TR]
[TR]
[TD]5a[/TD]
[TD="align: right"]87%[/TD]
[TD="align: right"]87%[/TD]
[TD="align: right"]85%[/TD]
[TD="align: right"]85%[/TD]
[TD="align: right"]84%[/TD]
[TD="align: right"]84%[/TD]
[/TR]
</tbody>[/TABLE]


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

 

[AlanY]

try this

Book1
	A	B	C	D	E	F	G
1	Current Level	Foundation (%)
2		SS Biology Paper 1	SS Biology Paper 2	SS Chemistry Paper 1	SS Chemistry Paper 2	SS Physics Paper 1	SS Physics Paper 2
3	U	0%	0%	0%	0%	0%	0%
4	1c	10%	10%	9%	9%	8%	8%
5	1b	14%	14%	13%	13%	12%	12%
6	1a	18%	18%	17%	17%	15%	15%
7	2c	23%	23%	21%	21%	18%	18%
8	2b	27%	27%	25%	25%	22%	22%
9	2a	31%	31%	28%	28%	25%	25%
10	3c	35%	35%	32%	32%	29%	29%
11	3b	39%	39%	36%	36%	33%	33%
12	3a	43%	43%	40%	40%	36%	36%
13	4c	48%	48%	44%	44%	39%	39%
14	4b	51%	51%	47%	47%	44%	44%
15	4a	55%	55%	51%	51%	48%	48%
16	5c	58%	58%	54%	54%	53%	53%
17	5b	73%	73%	70%	70%	68%	68%
18	5a	87%	87%	85%	85%	84%	84%
19
20	SS Physics Paper 1	40%	4c
Sheet5

Cell Formulas
Range	Formula
C20	=INDEX($A$3:$A$18,MATCH(B20,INDEX($B$3:$G$18,,MATCH(A20,$B$2:$G$2,0))),0)

 

[steve the fish]

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)

 

[dobby1303]

Thanks, it works perfectly!

 

[gaz_chops]

Another option

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