Lookup value from different range

[Abdulkhadar]

Hello Excel Experts,
I want to find the grade. first lookup the maximum no. from A column from first row, then lookup the column B value from the prescribed range.

Maximum	10	20	30	40	60	100
	RANGE	GRADE	RANGE	GRADE	RANGE	GRADE	RANGE	GRADE	RANGE	GRADE	RANGE	GRADE
	09	10	A+	18	20	A+	27	30	A+	36	40	A+	54	60	A+	90	100	A+
	07	08	A	14	17	A	21	26	A	28	35	A	42	53	A	70	89	A
	05	06	B+	10	13	B+	15	20	B+	20	27	B+	30	41	B+	50	69	B+
	03	04	B	06	9	B	09	14	B	12	19	B	18	29	B	30	49	B
	00	2	C	00	5	C	00	8	C	00	11	C	00	17	C	00	29	C

<tbody>
</tbody>

My goal is

	A	B	C
1	Maximum	Obt. Marks	Grade
2	10	9	A+
3	20	9	B
4	30	9	B
5	60	30	B+
6	100	70	A

<tbody>
</tbody>

Thanks in advance

 

[Scott T]

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Maximum	10			20			30			40			60			100
2		RANGE		GRADE	RANGE		GRADE	RANGE		GRADE	RANGE		GRADE	RANGE		GRADE	RANGE		GRADE
3		9	10	A+	18	20	A+	27	30	A+	36	40	A+	54	60	A+	90	100	A+
4		7	8	A	14	17	A	21	26	A	28	35	A	42	53	A	70	89	A
5		5	6	B+	10	13	B+	15	20	B+	20	27	B+	30	41	B+	50	69	B+
6		3	4	B	6	9	B	9	14	B	12	19	B	18	29	B	30	49	B
7		0	2	C	0	5	C	0	8	C	0	11	C	0	17	C	0	29	C
8
9
10
11	Maximum	Obt. Marks	Grade
12	10	9	A+
13	20	9	B
14	30	9	B
15	60	30	B+
16	100	70	A
Sheet4

Cell Formulas
Range	Formula
C12	=INDEX(CHOOSE(LOOKUP(A12,{10,20,30,40,60,100},{1,2,3,4,5,6}),$D$3:$D$7,$G$3:$G$7,$J$3:$J$7,$M$3:$M$7,$P$3:$P$7,$S$3:$S$7),MATCH(B12,CHOOSE(LOOKUP(A12,{10,20,30,40,60,100},{1,2,3,4,5,6}),$C$3:$C$7,$F$3:$F$7,$I$3:$I$7,$L$3:$L$7,$O$3:$O$7,$R$3:$R$7),-1))
C13	=INDEX(CHOOSE(LOOKUP(A13,{10,20,30,40,60,100},{1,2,3,4,5,6}),$D$3:$D$7,$G$3:$G$7,$J$3:$J$7,$M$3:$M$7,$P$3:$P$7,$S$3:$S$7),MATCH(B13,CHOOSE(LOOKUP(A13,{10,20,30,40,60,100},{1,2,3,4,5,6}),$C$3:$C$7,$F$3:$F$7,$I$3:$I$7,$L$3:$L$7,$O$3:$O$7,$R$3:$R$7),-1))
C14	=INDEX(CHOOSE(LOOKUP(A14,{10,20,30,40,60,100},{1,2,3,4,5,6}),$D$3:$D$7,$G$3:$G$7,$J$3:$J$7,$M$3:$M$7,$P$3:$P$7,$S$3:$S$7),MATCH(B14,CHOOSE(LOOKUP(A14,{10,20,30,40,60,100},{1,2,3,4,5,6}),$C$3:$C$7,$F$3:$F$7,$I$3:$I$7,$L$3:$L$7,$O$3:$O$7,$R$3:$R$7),-1))
C15	=INDEX(CHOOSE(LOOKUP(A15,{10,20,30,40,60,100},{1,2,3,4,5,6}),$D$3:$D$7,$G$3:$G$7,$J$3:$J$7,$M$3:$M$7,$P$3:$P$7,$S$3:$S$7),MATCH(B15,CHOOSE(LOOKUP(A15,{10,20,30,40,60,100},{1,2,3,4,5,6}),$C$3:$C$7,$F$3:$F$7,$I$3:$I$7,$L$3:$L$7,$O$3:$O$7,$R$3:$R$7),-1))
C16	=INDEX(CHOOSE(LOOKUP(A16,{10,20,30,40,60,100},{1,2,3,4,5,6}),$D$3:$D$7,$G$3:$G$7,$J$3:$J$7,$M$3:$M$7,$P$3:$P$7,$S$3:$S$7),MATCH(B16,CHOOSE(LOOKUP(A16,{10,20,30,40,60,100},{1,2,3,4,5,6}),$C$3:$C$7,$F$3:$F$7,$I$3:$I$7,$L$3:$L$7,$O$3:$O$7,$R$3:$R$7),-1))

 

[bosco_yip]

Another option,

In C12, formula copied down to C16 :

=INDEX(OFFSET(A$1,2,MATCH($A12,B$1:S$1,0)+2,5),MATCH(B12,OFFSET(A$1,2,MATCH($A12,B$1:S$1,0)+1,5),-1))

Regards
Bosco

 

[Abdulkhadar]

Excel 2010

Cell Formula C12 =INDEX(CHOOSE(LOOKUP(A12,{10,20,30,40,60,100},{1,2,3,4,5,6}),$D$3:$D$7,$G$3:$G$7,$J$3:$J$7,$M$3:$M$7,$P$3:$P$7,$S$3:$S$7),MATCH(B12,CHOOSE(LOOKUP(A12,{10,20,30,40,60,100},{1,2,3,4,5,6}),$C$3:$C$7,$F$3:$F$7,$I$3:$I$7,$L$3:$L$7,$O$3:$O$7,$R$3:$R$7),-1)) C13 =INDEX(CHOOSE(LOOKUP(A13,{10,20,30,40,60,100},{1,2,3,4,5,6}),$D$3:$D$7,$G$3:$G$7,$J$3:$J$7,$M$3:$M$7,$P$3:$P$7,$S$3:$S$7),MATCH(B13,CHOOSE(LOOKUP(A13,{10,20,30,40,60,100},{1,2,3,4,5,6}),$C$3:$C$7,$F$3:$F$7,$I$3:$I$7,$L$3:$L$7,$O$3:$O$7,$R$3:$R$7),-1)) C14 =INDEX(CHOOSE(LOOKUP(A14,{10,20,30,40,60,100},{1,2,3,4,5,6}),$D$3:$D$7,$G$3:$G$7,$J$3:$J$7,$M$3:$M$7,$P$3:$P$7,$S$3:$S$7),MATCH(B14,CHOOSE(LOOKUP(A14,{10,20,30,40,60,100},{1,2,3,4,5,6}),$C$3:$C$7,$F$3:$F$7,$I$3:$I$7,$L$3:$L$7,$O$3:$O$7,$R$3:$R$7),-1)) C15 =INDEX(CHOOSE(LOOKUP(A15,{10,20,30,40,60,100},{1,2,3,4,5,6}),$D$3:$D$7,$G$3:$G$7,$J$3:$J$7,$M$3:$M$7,$P$3:$P$7,$S$3:$S$7),MATCH(B15,CHOOSE(LOOKUP(A15,{10,20,30,40,60,100},{1,2,3,4,5,6}),$C$3:$C$7,$F$3:$F$7,$I$3:$I$7,$L$3:$L$7,$O$3:$O$7,$R$3:$R$7),-1)) C16 =INDEX(CHOOSE(LOOKUP(A16,{10,20,30,40,60,100},{1,2,3,4,5,6}),$D$3:$D$7,$G$3:$G$7,$J$3:$J$7,$M$3:$M$7,$P$3:$P$7,$S$3:$S$7),MATCH(B16,CHOOSE(LOOKUP(A16,{10,20,30,40,60,100},{1,2,3,4,5,6}),$C$3:$C$7,$F$3:$F$7,$I$3:$I$7,$L$3:$L$7,$O$3:$O$7,$R$3:$R$7),-1)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Thanks for quick reply, it works perfect upto range 60 i.e N1, but for 100 range range every marks grade shows A+.

 

[Abdulkhadar]

Another option,

In C12, formula copied down to C16 :

=INDEX(OFFSET(A$1,2,MATCH($A12,B$1:S$1,0)+2,5),MATCH(B12,OFFSET(A$1,2,MATCH($A12,B$1:S$1,0)+1,5),-1))

Regards
Bosco

Nice, Thanks for quick reply, it works perfect upto range 60 i.e N1, but This formula also not work for 100 range range every marks grade shows A+.

 

[Scott T]

Both formulas work for me. Make sure your cell references are correct.

 

[Abdulkhadar]

Both formulas work for me. Make sure your cell references are correct.

K Thanks sir, I dont know why this formula not works only for 10 & 100 in MS office-10 in my PC. Now it works perfectly in MS Office-7.
Thanks once again.