Name Marks
John 93 Jim's Marks
Jenny 88 Index Match is an alternative to Vlook
Mark 47
Jim 65
Michael 41
Hannah 47
Kane 32
Alan 57
Angela 89
Chris 25
Marks Name
93 John
88 Jenny
47 Mark
65 Jim Jim
41 Michael 65
47 Hannah
32 Kane
57 Alan
89 Angela
25 Chris
tch is an alternative to Vlookup
Kumar
Name John Jenny Mark Jim Michael Hannah Kane Alan Angela
Marks 93 88 47 65 41 47 32 57 89
Jim's Marks
Chris
25
Store Sales Manager
1 679 John
2 568 Jenny Hannah
3 534 Mark
4 679 Jim
5 757 Michael
6 798 Hannah
7 544 Kane
8 751 Alan
9 598 Angela
10 571 Chris Index and Match is an alter
Manager Sales
John 679
Jenny 568
Mark 534
Jim 679
Michael 757
Hannah 798
Kane 544
Alan 751
Angela 598
Chris 571
Sales Manager Hannah
679 John 798
568 Jenny
534 Mark 0
679 Jim
757 Michael
798 Hannah
544 Kane
751 Alan
598 Angela
571 Chris
x and Match is an alternative to Vlookup
Name Math Physics Chemistry
John 71 53 89 Math Physics
Jenny 88 45 55 Angela
Mark 44 71 54 Mark
Jim 97 70 73 Jim
Michael 47 34 38
Hannah 88 75 90
Kane 87 42 54
Alan 27 37 56 Math Physics
Angela 35 89 57 Angela
Chris 67 54 82 Mark
Jim
Chemistry
Chemistry
First Name Last Name Math
John Haynes 71 First Name Last Name Score
Jim Frost 88 Jim Hansen
Mark Long 44
Jim Hansen 97
Michael Hansen 47
Hannah Bass 88
Kane Becker 87
Alan Lockard 27
Angela Lott 35
Chris Whit 67
Name Math Physics Chemistry
John 71 53 89
Jenny 88 45 55 Total
Mark 44 28 54 Michael
Jim 97 70 73
Michael 47 34 38
Hannah 88 75 90
Kane 87 42 54
Alan 27 37 56
Angela 35 89 57
Chris 67 54 82
Name A1 A2 A3
Jan 71 53 89
Feb 88 45 55
Mar 44 28 54
Apr 97 70 73
May 47 34 38
Jun 88 75 90
Jul 87 42 54
Aug 27 37 56
Sep 35 89 57
Oct 67 54 82
Nov 14 62 24
Dec 45 67 84
SUMPRODUCT(INDEX($B$2:$D$11,MATCH(F4,$A$2:$A$11,0)
SUMPRODUCT(VLOOKUP(F4,$A$2:$D$11,{2,3,4},0))
$11,MATCH(F4,$A$2:$A$11,0),))
A$2:$D$11,{2,3,4},0))
Name Math Grade Grade
John 71 Marks Range
Jenny 27 0
Mark 44 33
Jim 92 50
Michael 47 70
Hannah 99 80
Kane 87 90
Alan 97
Angela 35
Chris 67
Grade
F
E
D
C
B
A
Name Math Grade
John 34 Marks Range Grade
Jenny 71 91 to 100 100 A
Mark 72 81 to 90 90 B
Jim 97 71 to 79 80 C
Michael 47 51 to 70 70 D
Hannah 88 34 to 50 50 E
Kane 87 0 to 33 33 F
Alan 27
Angela 34
Chris 67
Name Marks
Jim 93 Marks
Jenny 88 MARK
Mark 47
jim 65
Michael 41
Hannah 47 Marks
MARK 32 MARK
Alan 57
Angela 89
Chris 25
Name
Jim
Jenny
Mark
jim
Michael
Hannah
MARK
Alan
Angela
Chris
Name Experience (Years) Required Experience
John 9.3 2.5
Tom 1.7
Arjun 2.4
Greg 24.3
Martha 17
Xi 13.7
Jenny 2.9
Bob 11.7
Marie 4.5
Amy 12.3
Charlie 2.1
Sam 2.1
Bruce 2.4
Jamie 5.4
Amit 1.9
Rakesh 3.4
Rajesh 3.7
Abhay 6.9
Vinita 20.1
Punita 12.9
Sunita 3
Ajay 2
Vikram 6
Shaym 4
Melvin 2.8
Right Person for the Job Arjun 2.4
Bruce 2.4
Charlie 2.1
Sam 2.1
2.6
2.3
2.4
Name Mkt Cap Name Mkt Cap
Inc. Apple 926.9 Apple
[Link], Inc. 777.8 Microsoft
Alphabet Alphabet Inc. 766.4 Alibaba
Corporation Microsoft 750.6
Facebook [Link] 541.5
Alibaba Group Holding Limited 499.4
Berkshire Hathaway Inc 491.9
Tencent Holdings Limited 491.3
J.P. Morgan Chase & Co. 387.7
AB* starts with AB
*AB ends with AB
*AB* content
Unit Test
Name Math Physics Chemistry
John 71 53 89 Unit Test Mid Term
Jenny 88 45 55 Math Physics
Mark 44 28 54 Jenny
Jim 97 70 73 Michael
Michael 47 34 38
Mid Term
Name Math Physics Chemistry
John 79 69 63 Unit Test Mid Term
Jenny 47 71 70 Math Physics
Mark 95 63 37 Jenny
Jim 98 89 41 Jim
Michael 71 60 88
Final
Name Math Physics Chemistry
John 70 44 66
Jenny 51 56 71
Mark 51 93 55
Jim 46 40 64
Michael 50 90 85
Test Subjects Students
Unit Test Math John
Final Mid Term Physics Jenny
Chemistry Final Chemistry Mark
Jim
Michael
Final
Chemistry Jenny Jim Michael
Unit Test Math 88 97 47
Mid Term Physics 71 89 60
Final Chemistry 71 64 85
88 71 71
