yet another VLOOKUP problem

  • Thread starter Thread starter Stuart House
  • Start date Start date
S

Stuart House

I have 3 columns of data
the first with students'names, the second is the subject and the third the
grade.

Students names appear more than once because they do more than one subject

e.g.

Joe Bloggs..........History.........C
Joe Bloggs..........Maths..........B
Joe Bloggs..........Biology........D
Mary Bloggs.......French.........A
Mary Bloggs.......Biology........F

etc.

I'd like to do a lookup table to find e.g. Joe Bloggs' Maths results or Mary
Bloggs Biology etc, but I can't work out how to do it.


I'd be grateful for any help

Thanks very much

Stuart House
 
Let B2:D6 house the grade data.

In A2 enter & copy down:

=B2&CHAR(127)&C2

Now use:

=VLOOKUP(F2&CHAR(127)&G2,$A$2:$D$6,4,0)

in order to fetch the grade of the student in F2 on the subject in G2.
 
A macro assigned to a button would make it easier.

Sub Macro6()
myname = InputBox("Enter Name")
mysub = InputBox("Enter Subject")
With Range("c14:e18")
.AutoFilter Field:=1, Criteria1:=myname '"Joe Blow"
.AutoFilter Field:=2, Criteria1:=mysub '"math"
End With
End Sub

Sub Macro7() ' to unfilter
Selection.AutoFilter
End Sub
 
Back
Top