# Match & Vlookup Function

R

#### roter789

The problem i have trouble with is the formula in getting the lette
mark for each of Faculty. I am not familiar yet in using the Match an
Vlookup Function, so I am not sure where is the mistake in the formul
Here is the formula

Here is the table:

0 F F F
40 E F F
50 D Pass F
55 D+ C- F
60 C C Pass
65 C+ C+ Pass
70 B B- C
75 B+ B C
80 A B+ B
85 A A- B
90 A+ A A
95 A+ A+ A

And here is the marks of each student according to their faculty with
raw mark:
BINCE Arts 89
BUSHBY Arts 47
CAMPBELL Science 78
CUKIER Arts 72
DA-COSTA Science 85
DHILLON Arts 63
FERNANDES Science 48
HALL Science 48
HUNT Arts 76
IVANOVA Science 75
JOHNSON Science 75
JOLLY Arts 76
KALICHARAN Arts 78
LUSSIER Arts 74
MUHIC Science 65
NACCI Arts 68
OLDFIELD Science 63
PARK Arts 62
SABHARWAL Arts 61
SHERRITT Arts 41
SIN Science 49
SMYLIE Arts 72
TROTTER Science 73
WONG Science 87
WRIGHT Arts 85

Thanks for the help in advance,
Reube

D

#### dlamarche

Hello, Roter,

The following formula will do the job:
=VLOOKUP(C19,\$A\$2:\$D\$13,MATCH(B19,\$A\$1:\$D\$1,0),1)

I copied your lookup table in A1 and the student scores in A18.

Match() looks for the Faculty in B19 and finds it in the first row of the
lookup table. It returns the rank of the word. For example for Arts it will
return 2, Science: 3 and Graduate: 3.

VLookup will used that information in the 3rd argument Col_index_num. So

VLookup(C19 is the first student score
\$A\$2:\$D\$13 is the whole lookup table
MATCH(B19,\$A\$1:\$D\$1,0) finds the position of the faculty in the 1st row of
the lookup table and returns its position
1 is for a closest match as opposed to an exact match.

Mutch simpler and no IF()

Hope this helped,