Vlookup problem

[Garry]

Hi
I have a set of data where colum A has a lerner Name
Column B has the different units that learner is enrolled for Example
English, Maths, Physics, Biology (Max 4 units for each learner).
Column C has marks for each unit.

So one learner can have 4 Rows and 3 columns, now when i want to lookup
function, I want it to lookup for a learner Garry and find his marks in each
unit.

How can i do it? Please help, it will be much appriciated.! Thanka

 

[Access101]

Does sorting the data by learning bring up the results you're looking for?

 

[Ken Johnson]

Hi
I have a set of data where colum A has a lerner Name
Column B has the different units that learner is enrolled for Example
English, Maths, Physics, Biology (Max 4 units for each learner).
Column C has marks for each unit.

So one learner can have 4 Rows and 3 columns, now when i want to lookup
function, I want it to lookup for a learner Garry and find his marks in each
unit.

How can i do it? Please help, it will be much appriciated.! Thanka

One way...

Put this in D2 and fill down to bottom of data...

=IF(A2=$E$1,ROW(),"")

Column D is a helper column and could be hidden.

Use E1 for typing the name of the student whose units and results you
want to look up.

Put this in F1 and fill down to F4...

=IF(ISERROR(SMALL($D$1:$D$8,ROWS($1:1))),"",INDEX($B$1:$B$8,MATCH(SMALL
($D$1:$D$8,ROWS($1:1)),$D$1:$D$8,0)))

Column F will show the Units done by the student whose name is in E1.

Put this in G1 and fill down to G4 to show the unit scores...

=IF(ISERROR(SMALL($D$1:$D$8,ROWS($1:1))),"",INDEX($C$1:$C$8,MATCH(SMALL
($D$1:$D$8,ROWS($1:1)),$D$1:$D$8,0)))

Ken Johnson

 

[Ken Johnson]

I forgot to add that you can either change all the 8s in the last two
formulas to suit the number of rows in your data or use entire columns
like...

=IF(ISERROR(SMALL($D:$D,ROWS($1:1))),"",INDEX($B:$B,MATCH(SMALL($D:
$D,ROWS($1:1)),$D:$D,0)))

Ken Johnson