yet another VLOOKUP problem

[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

 

[Aladin Akyurek]

Let B26 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.

 

[Don Guillett]

how about data>autofilter on name and then subject.

 

[Don Guillett]

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