Vlookup for 2nd or 3rd Row

amkkhan

Board Regular
Joined
Dec 11, 2021
Messages
65
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
 

amkkhan

Board Regular
Joined
Dec 11, 2021
Messages
65
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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))
Thanks...

It really worked but now the main problem is whenever I make a copy of my file (for the backup purpose) or place it at a different destination with different name.
The formulas in result card changes and the entire path of file is mentioned before sheet.
Is there any way to get rid of it
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,549
Office Version
  1. 2016
Platform
  1. Windows
I don't understand. What does that look like?
 

Forum statistics

Threads
1,186,059
Messages
5,955,613
Members
438,205
Latest member
gaose77777

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
Top