| Book1 | ||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | |||
| 1 | Report Card | |||||||||||||||||
| 2 | ||||||||||||||||||
| 3 | Name | Nick | Roll No | 2 | ||||||||||||||
| 4 | ||||||||||||||||||
| 5 | R.No | Name | Exam | Exam : 1 | ||||||||||||||
| 6 | Total | Obtained | %age | Grade | Obtained Marks | 190 | ||||||||||||
| 7 | 1 | Joe | Ex:1 : 50% | 250 | 210 | 84.00 | A+ | Obtained %age | 38 | |||||||||
| 8 | Ex:2 : 50% | 250 | 200 | 80.00 | A+ | Obtained Grade | E | |||||||||||
| 9 | Final Result | G.Total | 500 | 410.00 | 82.00 | A+ | ||||||||||||
| 10 | 2 | Nick | Ex:1 : 50% | 500 | 190 | 38.00 | E | Exam : 2 | ||||||||||
| 11 | Ex:2 : 50% | 500 | 227 | 45.40 | D | Obtained Marks | 190 | These Cells should get information of "Ex:2 : 50%" from the left table and siplay here using Vlookup or Hlookup | ||||||||||
| 12 | Final Result | G.Total | 500 | 417.00 | 83.40 | A+ | Obtained %age | 38 | ||||||||||
| 13 | 3 | James | Ex:1 : 50% | 500 | 244 | 48.80 | D | Obtained Grade | E | |||||||||
| 14 | Ex:2 : 50% | 500 | 125 | 25.00 | F | |||||||||||||
| 15 | Final Result | G.Total | 500 | 369.00 | 73.80 | A | Grand Total | |||||||||||
| 16 | Obtained Marks | 190 | These Cells should get information of "G.Total" from the left table and siplay here using Vlookup or Hlookup | |||||||||||||||
| 17 | Obtained %age | 38 | ||||||||||||||||
| 18 | Obtained Grade | E | ||||||||||||||||
Sheet1 | ||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| J3 | J3 | =VLOOKUP(L3,$A$5:$G$15,2,0) |
| F7:F15 | F7 | =IF(E7="","",(E7/D7)*100) |
| G7:G15 | G7 | =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:D15 | D8 | =D7 |
| K6 | K6 | =VLOOKUP(L3,$A$5:$G$15,5,0) |
| K7,K17,K12 | K7 | =VLOOKUP($L$3,$A$5:$G$15,6,0) |
| K8,K18,K13 | K8 | =VLOOKUP($L$3,$A$5:$G$15,7,0) |
| E9,E15,E12 | E9 | =SUM(E7:E8) |
| K11 | K11 | =VLOOKUP(L3,$A$5:$G$15,5,0) |
| K16 | K16 | =VLOOKUP(L3,$A$5:$G$15,5,0) |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| F7:F15 | Cell Value | <40 | text | NO |
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
