Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Vlookup problem
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Vlookup problem
Does sorting the data by learning bring up the results you're looking for?
"Garry" wrote: 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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Vlookup problem
On Jan 22, 8:48*am, Garry wrote:
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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Vlookup problem
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup Problem | Excel Worksheet Functions | |||
Vlookup problem | New Users to Excel | |||
VLOOKUP problem with using a - | Excel Worksheet Functions | |||
vlookup problem | Excel Discussion (Misc queries) | |||
VLookup problem | Excel Worksheet Functions |