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.

<tbody>
[TD="class: xl65"]Maximum[/TD]
[TD="class: xl66, colspan: 3"]10[/TD]
[TD="class: xl66, width: 108, colspan: 3"]20[/TD]
[TD="class: xl66, width: 108, colspan: 3"]30[/TD]
[TD="class: xl66, width: 108, colspan: 3"]40[/TD]
[TD="class: xl66, width: 108, colspan: 3"]60[/TD]
[TD="class: xl66, width: 108, colspan: 3"]100[/TD]

[TD="class: xl65"][/TD]
[TD="class: xl66, colspan: 2"]RANGE[/TD]
[TD="class: xl70"]GRADE[/TD]
[TD="class: xl66, colspan: 2"]RANGE[/TD]
[TD="class: xl70"]GRADE[/TD]
[TD="class: xl66, colspan: 2"]RANGE[/TD]
[TD="class: xl70"]GRADE[/TD]
[TD="class: xl66, colspan: 2"]RANGE[/TD]
[TD="class: xl70"]GRADE[/TD]
[TD="class: xl66, colspan: 2"]RANGE[/TD]
[TD="class: xl70"]GRADE[/TD]
[TD="class: xl66, colspan: 2"]RANGE[/TD]
[TD="class: xl70"]GRADE[/TD]

[TD="class: xl65"][/TD]
[TD="class: xl68"]09[/TD]
[TD="class: xl66"]10[/TD]
[TD="class: xl66"]A+[/TD]
[TD="class: xl68"]18[/TD]
[TD="class: xl66"]20[/TD]
[TD="class: xl66"]A+[/TD]
[TD="class: xl68"]27[/TD]
[TD="class: xl66"]30[/TD]
[TD="class: xl66"]A+[/TD]
[TD="class: xl68"]36[/TD]
[TD="class: xl66"]40[/TD]
[TD="class: xl66"]A+[/TD]
[TD="class: xl68"]54[/TD]
[TD="class: xl66"]60[/TD]
[TD="class: xl66"]A+[/TD]
[TD="class: xl68"]90[/TD]
[TD="class: xl66"]100[/TD]
[TD="class: xl66"]A+[/TD]

[TD="class: xl67"][/TD]
[TD="class: xl68"]07[/TD]
[TD="class: xl69"]08[/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl68"]14[/TD]
[TD="class: xl69"]17[/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl68"]21[/TD]
[TD="class: xl66"]26[/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl68"]28[/TD]
[TD="class: xl66"]35[/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl68"]42[/TD]
[TD="class: xl66"]53[/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl68"]70[/TD]
[TD="class: xl66"]89[/TD]
[TD="class: xl66"]A[/TD]

[TD="class: xl65"][/TD]
[TD="class: xl68"]05[/TD]
[TD="class: xl69"]06[/TD]
[TD="class: xl66"]B+[/TD]
[TD="class: xl68"]10[/TD]
[TD="class: xl69"]13[/TD]
[TD="class: xl66"]B+[/TD]
[TD="class: xl68"]15[/TD]
[TD="class: xl66"]20[/TD]
[TD="class: xl66"]B+[/TD]
[TD="class: xl68"]20[/TD]
[TD="class: xl66"]27[/TD]
[TD="class: xl66"]B+[/TD]
[TD="class: xl68"]30[/TD]
[TD="class: xl66"]41[/TD]
[TD="class: xl66"]B+[/TD]
[TD="class: xl68"]50[/TD]
[TD="class: xl66"]69[/TD]
[TD="class: xl66"]B+[/TD]

[TD="class: xl65"][/TD]
[TD="class: xl68"]03[/TD]
[TD="class: xl69"]04[/TD]
[TD="class: xl66"]B[/TD]
[TD="class: xl68"]06[/TD]
[TD="class: xl69"]9[/TD]
[TD="class: xl66"]B[/TD]
[TD="class: xl68"]09[/TD]
[TD="class: xl66"]14[/TD]
[TD="class: xl66"]B[/TD]
[TD="class: xl68"]12[/TD]
[TD="class: xl66"]19[/TD]
[TD="class: xl66"]B[/TD]
[TD="class: xl68"]18[/TD]
[TD="class: xl66"]29[/TD]
[TD="class: xl66"]B[/TD]
[TD="class: xl68"]30[/TD]
[TD="class: xl66"]49[/TD]
[TD="class: xl66"]B[/TD]

[TD="class: xl65"][/TD]
[TD="class: xl69"]00[/TD]
[TD="class: xl69"]2[/TD]
[TD="class: xl66"]C[/TD]
[TD="class: xl69"]00[/TD]
[TD="class: xl69"]5[/TD]
[TD="class: xl66"]C[/TD]
[TD="class: xl69"]00[/TD]
[TD="class: xl66"]8[/TD]
[TD="class: xl66"]C[/TD]
[TD="class: xl69"]00[/TD]
[TD="class: xl66"]11[/TD]
[TD="class: xl66"]C[/TD]
[TD="class: xl69"]00[/TD]
[TD="class: xl66"]17[/TD]
[TD="class: xl66"]C[/TD]
[TD="class: xl69"]00[/TD]
[TD="class: xl66"]29[/TD]
[TD="class: xl66"]C[/TD]

</tbody>

My goal is

<tbody>
[TD="class: xl66"][/TD]
[TD="class: xl66"]A[/TD]
[TD="class: xl66, width: 82"]B[/TD]
[TD="class: xl66, width: 101"]C[/TD]

[TD="class: xl66"]1[/TD]
[TD="class: xl65"]Maximum[/TD]
[TD="class: xl65"]Obt. Marks[/TD]
[TD="class: xl65"]Grade[/TD]

[TD="class: xl66"]2[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]9[/TD]
[TD="class: xl65"]A+[/TD]

[TD="class: xl66"]3[/TD]
[TD="class: xl65"]20[/TD]
[TD="class: xl65"]9[/TD]
[TD="class: xl65"]B[/TD]

[TD="class: xl66"]4[/TD]
[TD="class: xl65"]30[/TD]
[TD="class: xl65"]9[/TD]
[TD="class: xl65"]B[/TD]

[TD="class: xl66"]5[/TD]
[TD="class: xl65"]60[/TD]
[TD="class: xl65"]30[/TD]
[TD="class: xl65"]B+[/TD]

[TD="class: xl66"]6[/TD]
[TD="class: xl65"]100[/TD]
[TD="class: xl65"]70[/TD]
[TD="class: xl65"]A[/TD]

</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[TABLE="width: 85%"]
<tbody>[TR]
[TD][TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C12[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C13[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C14[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C15[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C16[/TH]
[TD="align: left"]=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))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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.