Match & Vlookup Function

[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
i made.
Here is the formula
=IF(Fac="Arts",VLOOKUP(Fac,GradeTable,MATCH(Arts,Arts,Mark_Range),Arts),IF(Fac="Science",VLOOKUP(Fac,GradeTable,MATCH(Science,Science,Mark_Range),Science),VLOOKUP(Fac,GradeTable,MATCH(Graduate,Graduate,Mark_Range))))

Here is the table:

Arts Science Graduate
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:
Name Fac Score Grade
BERNIER Graduate 59
BINCE Arts 89
BUSHBY Arts 47
CAMPBELL Science 78
CORREIA Graduate 73
CUKIER Arts 72
DA-COSTA Science 85
DHILLON Arts 63
FERNANDES Science 48
FRASER Graduate 88
GRAVAS Graduate 95
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
PAULSON Graduate 91
ROY Graduate 86
RUSSO Graduate 84
SABHARWAL Arts 61
SHERRITT Arts 41
SIMPSON Graduate 64
SIN Science 49
SMYLIE Arts 72
THOMAS Graduate 89
TJANDRA Graduate 74
TROTTER Science 73
WONG Science 87
WRIGHT Arts 85

Thanks for the help in advance,
Reube

 

[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,