Vlookup for 2nd or 3rd Row

A

amkkhan

Board Regular
Joined
Dec 11, 2021
Messages
75
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Book1
ABCDEFGHIJKLMNOP
1Report Card
2
3NameNickRoll No2
4
5R.NoNameExamExam : 1
6TotalObtained%ageGradeObtained Marks190
71JoeEx:1 : 50%25021084.00A+Obtained %age38
8Ex:2 : 50%25020080.00A+Obtained GradeE
9Final ResultG.Total500410.0082.00A+
102NickEx:1 : 50%50019038.00EExam : 2
11Ex:2 : 50%50022745.40DObtained Marks190These Cells should get information of "Ex:2 : 50%" from the left table and siplay here using Vlookup or Hlookup
12Final ResultG.Total500417.0083.40A+Obtained %age38
133JamesEx:1 : 50%50024448.80DObtained GradeE
14Ex:2 : 50%50012525.00F
15Final ResultG.Total500369.0073.80AGrand Total
16Obtained Marks190These Cells should get information of "G.Total" from the left table and siplay here using Vlookup or Hlookup
17Obtained %age38
18Obtained GradeE
Sheet1
Cell Formulas
RangeFormula
J3J3=VLOOKUP(L3,$A$5:$G$15,2,0)
F7:F15F7=IF(E7="","",(E7/D7)*100)
G7:G15G7=IF(F7="","",IF(F7>=80,"A+",IF(F7>=70,"A",IF(F7>=60,"B",IF(F7>=50,"C",IF(F7>=40,"D",IF(F7>=33,"E","F")))))))
D8,D10:D15D8=D7
K6K6=VLOOKUP(L3,$A$5:$G$15,5,0)
K7,K17,K12K7=VLOOKUP($L$3,$A$5:$G$15,6,0)
K8,K18,K13K8=VLOOKUP($L$3,$A$5:$G$15,7,0)
E9,E15,E12E9=SUM(E7:E8)
K11K11=VLOOKUP(L3,$A$5:$G$15,5,0)
K16K16=VLOOKUP(L3,$A$5:$G$15,5,0)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F7:F15Cell Value<40textNO



How is it possible that I could get 2nd,3rd or 5th Row's Information of another sheet by using Vlookup or Hlookup.

I record I want is available in 2nd or 3rd row of the table.

You can better check by changing roll number to "2" or "3"

You better check XL2BB
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
You will have to use a combination of INDEX and MATCH to do this. The way you are using merged cells makes it impossible to use VLOOKUP. Nearly all Excel experts advise against using merged cells in nearly all situations.

For Exam 2 three values highlighted in yellow:

Excel Formula: 
=INDEX($C$7:$G$15,MATCH($L$3,$A$7:A$15,0)+1,3)
=INDEX($C$7:$G$15,MATCH($L$3,$A$7:A$15,0)+1,4)
=INDEX($C$7:$G$15,MATCH($L$3,$A$7:A$15,0)+1,5)

For Grand Total
Excel Formula: 
=INDEX($C$7:$G$15,MATCH($L$3,$A$7:A$15,0)+2,3)
=INDEX($C$7:$G$15,MATCH($L$3,$A$7:A$15,0)+2,4)
=INDEX($C$7:$G$15,MATCH($L$3,$A$7:A$15,0)+2,5)
 
Upvote 0
Hi AMKhan,

You are again using merged cells which requires additional calculations.

You cannot use VLOOKUP to match 2nd, 3rd, etc. matches except in special cases.

You can manipulate INDEX and MATCH by adding to the column and row for the INDEX to match your format.

AMKhan4.xlsx
ABCDEFGHIJKL
1Report Card
2
3NameNickRoll No2
4
5R.NoNameExamExam : 1
6TotalObtained%ageGradeObtained Marks190
71JoeEx:1 : 50%25021084A+Obtained %age38
8Ex:2 : 50%25020080A+Obtained GradeE
9Final ResultG.Total50041082A+
102NickEx:1 : 50%50019038EExam : 2
11Ex:2 : 50%50022745.4DObtained Marks227
12Final ResultG.Total50041783.4A+Obtained %age45.4
133JamesEx:1 : 50%50024448.8DObtained GradeD
14Ex:2 : 50%50012525F
15Final ResultG.Total50036973.8AGrand Total
16Obtained Marks417
17Obtained %age83.4
18Obtained GradeA+
1st
Cell Formulas
RangeFormula
J3J3=VLOOKUP(L3,$A$5:$G$15,2,0)
F7:F15F7=IF(E7="","",(E7/D7)*100)
G7:G15G7=IF(F7="","",IF(F7>=80,"A+",IF(F7>=70,"A",IF(F7>=60,"B",IF(F7>=50,"C",IF(F7>=40,"D",IF(F7>=33,"E","F")))))))
D8,D10:D15D8=D7
K6K6=INDEX($E$7:$G$15,MATCH($L$3,$A$7:$A$15,0),1)
K7K7=INDEX($E$7:$G$15,MATCH($L$3,$A$7:$A$15,0),2)
K8K8=INDEX($E$7:$G$15,MATCH($L$3,$A$7:$A$15,0),3)
E9,E15,E12E9=SUM(E7:E8)
K11K11=INDEX($E$7:$G$15,MATCH($L$3,$A$7:$A$15,0)+1,1)
K12K12=INDEX($E$7:$G$15,MATCH($L$3,$A$7:$A$15,0)+1,2)
K13K13=INDEX($E$7:$G$15,MATCH($L$3,$A$7:$A$15,0)+1,3)
K16K16=INDEX($E$7:$G$15,MATCH($L$3,$A$7:$A$15,0)+2,1)
K17K17=INDEX($E$7:$G$15,MATCH($L$3,$A$7:$A$15,0)+2,2)
K18K18=INDEX($E$7:$G$15,MATCH($L$3,$A$7:$A$15,0)+2,3)
 
Upvote 0
Toadstool said:
Hi AMKhan,

You are again using merged cells which requires additional calculations.

You cannot use VLOOKUP to match 2nd, 3rd, etc. matches except in special cases.

You can manipulate INDEX and MATCH by adding to the column and row for the INDEX to match your format.

AMKhan4.xlsx
ABCDEFGHIJKL
1Report Card
2
3NameNickRoll No2
4
5R.NoNameExamExam : 1
6TotalObtained%ageGradeObtained Marks190
71JoeEx:1 : 50%25021084A+Obtained %age38
8Ex:2 : 50%25020080A+Obtained GradeE
9Final ResultG.Total50041082A+
102NickEx:1 : 50%50019038EExam : 2
11Ex:2 : 50%50022745.4DObtained Marks227
12Final ResultG.Total50041783.4A+Obtained %age45.4
133JamesEx:1 : 50%50024448.8DObtained GradeD
14Ex:2 : 50%50012525F
15Final ResultG.Total50036973.8AGrand Total
16Obtained Marks417
17Obtained %age83.4
18Obtained GradeA+
1st
Cell Formulas
RangeFormula
J3J3=VLOOKUP(L3,$A$5:$G$15,2,0)
F7:F15F7=IF(E7="","",(E7/D7)*100)
G7:G15G7=IF(F7="","",IF(F7>=80,"A+",IF(F7>=70,"A",IF(F7>=60,"B",IF(F7>=50,"C",IF(F7>=40,"D",IF(F7>=33,"E","F")))))))
D8,D10:D15D8=D7
K6K6=INDEX($E$7:$G$15,MATCH($L$3,$A$7:$A$15,0),1)
K7K7=INDEX($E$7:$G$15,MATCH($L$3,$A$7:$A$15,0),2)
K8K8=INDEX($E$7:$G$15,MATCH($L$3,$A$7:$A$15,0),3)
E9,E15,E12E9=SUM(E7:E8)
K11K11=INDEX($E$7:$G$15,MATCH($L$3,$A$7:$A$15,0)+1,1)
K12K12=INDEX($E$7:$G$15,MATCH($L$3,$A$7:$A$15,0)+1,2)
K13K13=INDEX($E$7:$G$15,MATCH($L$3,$A$7:$A$15,0)+1,3)
K16K16=INDEX($E$7:$G$15,MATCH($L$3,$A$7:$A$15,0)+2,1)
K17K17=INDEX($E$7:$G$15,MATCH($L$3,$A$7:$A$15,0)+2,2)
K18K18=INDEX($E$7:$G$15,MATCH($L$3,$A$7:$A$15,0)+2,3)
Click to expand...
Book1.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
5Roll NoNameExamT. NameT.NameT.NameTotalAbsent inRank
6SubjectSubjectSubject
7MMOM%ageMMOM%ageMMOM%ageTotalObtained%ageGrade
81Joe1-T(5%)25102.025 2561.2751621.33F13Imagine This Result card is on another sheet
9M-T(25%)1004010.0100 5094.52504919.60F13Result Card
102-T(5%)25204.025 2561.2752634.67E13
11F-T(65%)1009058.5100 505065.025014056.00C114 Exam Stages Result
12Final ResultGrand Total10074.5100 10071.930014648.80D13I Want that if I Change Roll Number, That Specific Roll Numbers result should bedisplayed on result card sheet
132Nick1-T(5%)25234.625193.82500.0754256.00C-2NameSunny
14M-T(25%)1009022.51008020.0502311.525019377.20A-1
152-T(5%)25244.825255.025153.0756485.33A+-1Roll No 3Class
16F-T(65%)1004026.01003925.4503950.725011847.20D-3
17Final ResultGrand Total10057.910054.210065.230017759.08C01Final Exam Obt. Marks
183Sunny1-T(5%)25153.025204.025183.6755370.67A-1Final Exam Obt. %age
19M-T(25%)100 1007017.5502211.02509236.80E12Final Exam Pass Papers
202-T(5%)25204.025224.425132.6755573.33A-2Final Exam Grade
21F-T(65%)1004026.01005535.8502937.725012449.60D-2Final Exam Position(Rank)
22Final ResultGrand Total10033.010061.710054.930015049.85D02
234James1-T(5%)25 2591.825 75912.00F24
24M-T(25%)100 100102.550 250104.00F24
252-T(5%)25 2561.225 7568.00F24
26F-T(65%)100 10042.650 25041.60F24
27Final ResultGrand Total100 1008.1100 30082.70F24
Sheet2
Cell Formulas
RangeFormula
G27,G22,G17,G12,G7:I7G7=D7
J27,J22,J17,J12,J7:L7J7=D7
L8,I25,I23,F25,F23,I20,I18,F20,F18,I15,I13,F15,F13,I10,I8,F10,F8,L25,L23,L20,L18,L15,L13,L10L8=IF(K8="","",K8*0.2)
M8:M11M8=D8+G8+J8
M27,N23:N26,M22,N18:N21,M17,N13:N16,M12,N8:N11N8=SUM(E8,H8,K8)
O8:O27O8=IF(N8="","",(N8/M8)*100)
P8:P27P8=IF(O8="","",IF(O8>=80,"A+",IF(O8>=70,"A",IF(O8>=60,"B",IF(O8>=50,"C",IF(O8>=40,"D",IF(O8>=33,"E","F")))))))
Q23:Q26,Q18:Q21,Q13:Q16,Q8:Q11Q8=IF(N8="","",IF(COUNTBLANK(D8:L8)/2=0,"-",COUNTBLANK(D8:L8)/2))
R8:R27R8=IF(N8="","",COUNTIFS($C$8:$C$9999,$C8,$O$8:$O$9999,">"&O8)+1)
L9,L24,L19,L14L9=IF(K9="","",K9*0.5)
L11,L26,L21,L16L11=IF(K11="","",K11*1.3)
L12,L27,I27,F27,I22,F22,I17,F17,I12,F12,L22,L17L12=IF($N$11="","",IF(SUM(L8:L11)=0,"",SUM(L8:L11)))
N12,N27,N22,N17N12=SUM(F12,I12,L12)
Q12,Q27,Q22,Q17Q12=IF(N12="","",COUNTIF(D12:L12,"")-3)
M13,M23,M18M13=$M$8
M14,M24,M19M14=$M$9
M15,M25,M20M15=$M$10
M16,M26,M21M16=$M$11
F9,I24,F24,I19,F19,I14,F14,I9F9=IF(E9="","",E9*0.25)
F11,I26,F26,I21,F21,I16,F16,I11F11=IF(E11="","",E11*0.65)
D12,D27,D22,D17D12=IF(N11="","",100)
C13:C16C13=C8
D13,D23,D18D13=$D$8
D14,D24,D19D14=$D$9
D15,D25,D20D15=$D$10
D16,D26,D21D16=$D$11
G13,G23,G18G13=$G$8
G14,G24,G19G14=$G$9
G15,G25,G20G15=$G$10
G16,G26,G21G16=$G$11
J13,J23,J18J13=$J$8
J14,J24,J19J14=$J$9
J15,J25,J20J15=$J$10
J16,J26,J21J16=$J$11
W13W13=VLOOKUP(W15,$A$5:$R$27,2,0)
B17,B27,B22B17=$B$12
C17,C27,C22C17=$C$12
C18:C21C18=C8
C23:C26C23=C8
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D27:L27Cell Value<40textYES
D22:L22Cell Value<40textYES
D17:L17Cell Value<40textYES
K24,K26Cell Value<20textNO
K23,K25Cell Value<10textNO
K19,K21Cell Value<20textNO
K18,K20Cell Value<10textNO
K14,K16Cell Value<20textNO
K13,K15Cell Value<10textNO
H24,H26Cell Value<40textNO
H23,H25Cell Value<10textNO
H19,H21Cell Value<40textNO
H18,H20Cell Value<10textNO
H14,H16Cell Value<40textNO
H13,H15Cell Value<10textNO
H9,H11Cell Value<40textNO
H8,H10Cell Value<10textNO
E24,E26Cell Value<40textNO
E23,E25Cell Value<10textNO
E19,E21Cell Value<40textNO
E18,E20Cell Value<10textNO
E14,E16Cell Value<40textNO
E13,E15Cell Value<10textNO
O8:O27Cell Value<40textNO
K9,K11Cell Value<20textNO
E9,E11Cell Value<40textNO
E8,E10,K8,K10Cell Value<10textNO
D12:L12Cell Value<40textYES
 
Upvote 0
Toadstool said:
Hi AMKhan,

You are again using merged cells which requires additional calculations.

You cannot use VLOOKUP to match 2nd, 3rd, etc. matches except in special cases.

You can manipulate INDEX and MATCH by adding to the column and row for the INDEX to match your format.

AMKhan4.xlsx
ABCDEFGHIJKL
1Report Card
2
3NameNickRoll No2
4
5R.NoNameExamExam : 1
6TotalObtained%ageGradeObtained Marks190
71JoeEx:1 : 50%25021084A+Obtained %age38
8Ex:2 : 50%25020080A+Obtained GradeE
9Final ResultG.Total50041082A+
102NickEx:1 : 50%50019038EExam : 2
11Ex:2 : 50%50022745.4DObtained Marks227
12Final ResultG.Total50041783.4A+Obtained %age45.4
133JamesEx:1 : 50%50024448.8DObtained GradeD
14Ex:2 : 50%50012525F
15Final ResultG.Total50036973.8AGrand Total
16Obtained Marks417
17Obtained %age83.4
18Obtained GradeA+
1st
Cell Formulas
RangeFormula
J3J3=VLOOKUP(L3,$A$5:$G$15,2,0)
F7:F15F7=IF(E7="","",(E7/D7)*100)
G7:G15G7=IF(F7="","",IF(F7>=80,"A+",IF(F7>=70,"A",IF(F7>=60,"B",IF(F7>=50,"C",IF(F7>=40,"D",IF(F7>=33,"E","F")))))))
D8,D10:D15D8=D7
K6K6=INDEX($E$7:$G$15,MATCH($L$3,$A$7:$A$15,0),1)
K7K7=INDEX($E$7:$G$15,MATCH($L$3,$A$7:$A$15,0),2)
K8K8=INDEX($E$7:$G$15,MATCH($L$3,$A$7:$A$15,0),3)
E9,E15,E12E9=SUM(E7:E8)
K11K11=INDEX($E$7:$G$15,MATCH($L$3,$A$7:$A$15,0)+1,1)
K12K12=INDEX($E$7:$G$15,MATCH($L$3,$A$7:$A$15,0)+1,2)
K13K13=INDEX($E$7:$G$15,MATCH($L$3,$A$7:$A$15,0)+1,3)
K16K16=INDEX($E$7:$G$15,MATCH($L$3,$A$7:$A$15,0)+2,1)
K17K17=INDEX($E$7:$G$15,MATCH($L$3,$A$7:$A$15,0)+2,2)
K18K18=INDEX($E$7:$G$15,MATCH($L$3,$A$7:$A$15,0)+2,3)
Click to expand...

Thanks Toadstool you are always came forward for an excellent help Excellent ?.

Let me explain it more better because you know the background.
Actually it is Report Card sheet which is calling data from the sheet where you helped me last time about Ranking.
Now I wish to make report card of each student in such a way that if I type a specific roll Number, all the 4 exams stage's result+Grand total result and position should be displayed.
 
Upvote 0
amkkhan said:
Thanks Toadstool you are always came forward for an excellent help Excellent ?.

Let me explain it more better because you know the background.
Actually it is Report Card sheet which is calling data from the sheet where you helped me last time about Ranking.
Now I wish to make report card of each student in such a way that if I type a specific roll Number, all the 4 exams stage's result+Grand total result and position should be displayed.
Click to expand...


Thanks but when I apply this formula it don't work because my records are on a different sheet
 
Upvote 0
6StringJazzer said:
You will have to use a combination of INDEX and MATCH to do this. The way you are using merged cells makes it impossible to use VLOOKUP. Nearly all Excel experts advise against using merged cells in nearly all situations.

For Exam 2 three values highlighted in yellow:

Excel Formula: 
=INDEX($C$7:$G$15,MATCH($L$3,$A$7:A$15,0)+1,3)
=INDEX($C$7:$G$15,MATCH($L$3,$A$7:A$15,0)+1,4)
=INDEX($C$7:$G$15,MATCH($L$3,$A$7:A$15,0)+1,5)

For Grand Total
Excel Formula: 
=INDEX($C$7:$G$15,MATCH($L$3,$A$7:A$15,0)+2,3)
=INDEX($C$7:$G$15,MATCH($L$3,$A$7:A$15,0)+2,4)
=INDEX($C$7:$G$15,MATCH($L$3,$A$7:A$15,0)+2,5)
Click to expand...
Thanks but when I apply this formula it don't work because my records are on a different sheet
 
Upvote 0
So you want the report card generated from the sheet in the previous thread?

That sheet is too large to post as a mini-sheet so just confirm this is the data from which you'd like the Report Card generated.

1640895635613.png
 
Upvote 0
amkkhan said:
Thanks but when I apply this formula it don't work because my records are on a different sheet
Click to expand...
I responded to the question as you described it in your post. I'm not good at hitting a moving target.
 
Upvote 0
amkkhan said:
Book1.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
5Roll NoNameExamT. NameT.NameT.NameTotalAbsent inRank
6SubjectSubjectSubject
7MMOM%ageMMOM%ageMMOM%ageTotalObtained%ageGrade
81Joe1-T(5%)25102.025 2561.2751621.33F13Imagine This Result card is on another sheet
9M-T(25%)1004010.0100 5094.52504919.60F13Result Card
102-T(5%)25204.025 2561.2752634.67E13
11F-T(65%)1009058.5100 505065.025014056.00C114 Exam Stages Result
12Final ResultGrand Total10074.5100 10071.930014648.80D13I Want that if I Change Roll Number, That Specific Roll Numbers result should bedisplayed on result card sheet
132Nick1-T(5%)25234.625193.82500.0754256.00C-2NameSunny
14M-T(25%)1009022.51008020.0502311.525019377.20A-1
152-T(5%)25244.825255.025153.0756485.33A+-1Roll No 3Class
16F-T(65%)1004026.01003925.4503950.725011847.20D-3
17Final ResultGrand Total10057.910054.210065.230017759.08C01Final Exam Obt. Marks
183Sunny1-T(5%)25153.025204.025183.6755370.67A-1Final Exam Obt. %age
19M-T(25%)100 1007017.5502211.02509236.80E12Final Exam Pass Papers
202-T(5%)25204.025224.425132.6755573.33A-2Final Exam Grade
21F-T(65%)1004026.01005535.8502937.725012449.60D-2Final Exam Position(Rank)
22Final ResultGrand Total10033.010061.710054.930015049.85D02
234James1-T(5%)25 2591.825 75912.00F24
24M-T(25%)100 100102.550 250104.00F24
252-T(5%)25 2561.225 7568.00F24
26F-T(65%)100 10042.650 25041.60F24
27Final ResultGrand Total100 1008.1100 30082.70F24
Sheet2
Cell Formulas
RangeFormula
G27,G22,G17,G12,G7:I7G7=D7
J27,J22,J17,J12,J7:L7J7=D7
L8,I25,I23,F25,F23,I20,I18,F20,F18,I15,I13,F15,F13,I10,I8,F10,F8,L25,L23,L20,L18,L15,L13,L10L8=IF(K8="","",K8*0.2)
M8:M11M8=D8+G8+J8
M27,N23:N26,M22,N18:N21,M17,N13:N16,M12,N8:N11N8=SUM(E8,H8,K8)
O8:O27O8=IF(N8="","",(N8/M8)*100)
P8:P27P8=IF(O8="","",IF(O8>=80,"A+",IF(O8>=70,"A",IF(O8>=60,"B",IF(O8>=50,"C",IF(O8>=40,"D",IF(O8>=33,"E","F")))))))
Q23:Q26,Q18:Q21,Q13:Q16,Q8:Q11Q8=IF(N8="","",IF(COUNTBLANK(D8:L8)/2=0,"-",COUNTBLANK(D8:L8)/2))
R8:R27R8=IF(N8="","",COUNTIFS($C$8:$C$9999,$C8,$O$8:$O$9999,">"&O8)+1)
L9,L24,L19,L14L9=IF(K9="","",K9*0.5)
L11,L26,L21,L16L11=IF(K11="","",K11*1.3)
L12,L27,I27,F27,I22,F22,I17,F17,I12,F12,L22,L17L12=IF($N$11="","",IF(SUM(L8:L11)=0,"",SUM(L8:L11)))
N12,N27,N22,N17N12=SUM(F12,I12,L12)
Q12,Q27,Q22,Q17Q12=IF(N12="","",COUNTIF(D12:L12,"")-3)
M13,M23,M18M13=$M$8
M14,M24,M19M14=$M$9
M15,M25,M20M15=$M$10
M16,M26,M21M16=$M$11
F9,I24,F24,I19,F19,I14,F14,I9F9=IF(E9="","",E9*0.25)
F11,I26,F26,I21,F21,I16,F16,I11F11=IF(E11="","",E11*0.65)
D12,D27,D22,D17D12=IF(N11="","",100)
C13:C16C13=C8
D13,D23,D18D13=$D$8
D14,D24,D19D14=$D$9
D15,D25,D20D15=$D$10
D16,D26,D21D16=$D$11
G13,G23,G18G13=$G$8
G14,G24,G19G14=$G$9
G15,G25,G20G15=$G$10
G16,G26,G21G16=$G$11
J13,J23,J18J13=$J$8
J14,J24,J19J14=$J$9
J15,J25,J20J15=$J$10
J16,J26,J21J16=$J$11
W13W13=VLOOKUP(W15,$A$5:$R$27,2,0)
B17,B27,B22B17=$B$12
C17,C27,C22C17=$C$12
C18:C21C18=C8
C23:C26C23=C8
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D27:L27Cell Value<40textYES
D22:L22Cell Value<40textYES
D17:L17Cell Value<40textYES
K24,K26Cell Value<20textNO
K23,K25Cell Value<10textNO
K19,K21Cell Value<20textNO
K18,K20Cell Value<10textNO
K14,K16Cell Value<20textNO
K13,K15Cell Value<10textNO
H24,H26Cell Value<40textNO
H23,H25Cell Value<10textNO
H19,H21Cell Value<40textNO
H18,H20Cell Value<10textNO
H14,H16Cell Value<40textNO
H13,H15Cell Value<10textNO
H9,H11Cell Value<40textNO
H8,H10Cell Value<10textNO
E24,E26Cell Value<40textNO
E23,E25Cell Value<10textNO
E19,E21Cell Value<40textNO
E18,E20Cell Value<10textNO
E14,E16Cell Value<40textNO
E13,E15Cell Value<10textNO
O8:O27Cell Value<40textNO
K9,K11Cell Value<20textNO
E9,E11Cell Value<40textNO
E8,E10,K8,K10Cell Value<10textNO
D12:L12Cell Value<40textYES
Click to expand...

From this sheet the result would be:

AMKhan5.xlsx
ABCDEFGHI
1
2
3Result Card
4
54 Exam Stages Result
6
7NameSunny
8
9Roll No 3Class
10
11Final Exam Obt. Marks149.55
12Final Exam Obt. %age49.85
13Final Exam Pass Papers
14Final Exam GradeD
15Final Exam Position(Rank)2
16
Report Card
Cell Formulas
RangeFormula
C7C7=INDEX(Sheet2!$A$8:$R$27,MATCH('Report Card'!$C$9,Sheet2!$A$8:$A$27,0),COLUMN(Sheet2!$B$5))
E11,D7:E7D7=INDEX(Sheet2!$A$8:$R$27,MATCH('Report Card'!$C$9,Sheet2!$A$8:$A$27,0)+4,COLUMN(Sheet2!$N$7))
E12E12=INDEX(Sheet2!$A$8:$R$27,MATCH('Report Card'!$C$9,Sheet2!$A$8:$A$27,0)+4,COLUMN(Sheet2!$O$7))
E14E14=INDEX(Sheet2!$A$8:$R$27,MATCH('Report Card'!$C$9,Sheet2!$A$8:$A$27,0)+4,COLUMN(Sheet2!$P$7))
E15E15=INDEX(Sheet2!$A$8:$R$27,MATCH('Report Card'!$C$9,Sheet2!$A$8:$A$27,0)+4,COLUMN(Sheet2!$R$7))
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

M
  • Question Question
VLOOKUP?
Replies
1
Views
199
MeaclH
M
M
  • Solved
XLOOKUP help (maybe), not sure what function to use
Replies
4
Views
241
MeaclH
M
Sean15
  • Solved
Rewrite formula so 2nd, 3rd…occurrence of lookup value return blank cell
Replies
6
Views
248
Peter_SSs
Peter_SSs
A
  • Solved
vlookup formula to shows 2nd , 3rd, 4th result for same arrey value.
2
Replies
16
Views
506
Fluff
Fluff
F
  • Solved
How to show the nth occurance based on another column
Replies
6
Views
317
feni1388
F

Forum statistics

Threads
1,223,716
Messages
6,174,069
Members
452,542
Latest member
Bricklin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top