lookup?

johnpdavey

Board Regular
Joined
May 30, 2002
Messages
88
i have a set of data as follows
A1 name of student
B1 - I1 subjects studied, ie Ma, Eng, Sci
A2 total point score for student in A1
B2 - I2 grade for subjects, ie A, B, G
cell A3 next student......

i have this data for 160 students.
the grades run from A - G and then U
cells B1 to I1, (and B3 to I3 for the next student etc,) do not all have entries as some students did not do Sci or Pe or....
Cell B1, etc, does not always contain Ma as it depends on the combination of subjects taken.

i only need the Ma data. so what i would like is some way of extracting the student name, which will be in cells A1, A3, A5 etc and the grade associated with Ma, which will be dirctly under the Ma entry which could be anywhere in the row between columns B and I

eg<pre>
A B C D E F G H I
JOE Ma Eng Sci Pe Hi Ge Art
12 A C G U E E F
BOB Eng Sci Ma
6 E F U


i would like
JOE A
BOB U</pre>

thanks in advance for any help
This message was edited by Juan Pablo G. on 2002-08-27 13:54
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Juan
thanks for the help
it works great.
can you please explain something to me?
i can work out the cell references so i can use it on my data set but can you tell me what the ...)-1,o,2,256)2
and....)-1,0,1,256)0
do at the end of the ofset statement so that i can understand what is going on and adapt it for future use?
thanks again for the help
john
 
Upvote 0
Hi John,

1) Never, ever set up a spreadsheet like this again!! :) Only ever have one row per entry, one set of column headings & live with the spaces in your data.


2) This isn't that dissimilar to what Juan posted, I've just broken the stages up so you can see them rather than do it all at once:
Book5
ABCDEFGH
1JOEMaEngSciPeHiGeArt
212ACGUEEF
3BOBEngSciMa
46EFU
5
6WhatStartCell?WhatRow?WhatCol?Where'stheresult?What'stheresult?
7JOEA11:122:2A
8BOBA33:344:4U
9
Sheet1


Comments:


Names - you will need to extract a list of just your pupil's names so that we can work of a proper list (the rest assumes there are no duplicate names)

What Start Cell:

=ADDRESS(MATCH(A7,$A$1:$A$4,0),MATCH($A$7,$A$1:$A$4,0),4,1)

This gets the cell address of where a particular name is in Col A. Given that you haven't got that much data, you could change $A$1:$a$4 in the formula to A:A so that it would still work when you added more data.

What Row?

=RIGHT(B7,1)&":"&RIGHT(B7,1)

Just on way of getting the row that the subject headings are on for a particular pupil.

What Col?

=MATCH("Ma",INDIRECT(C7),0)

Hunts for the position of "Ma" in the range indirect(c7) which in this case in the row 1:1 (check out the help for indirect to see how it works)

Where's the result?

=RIGHT(B7,1)+1&":"&RIGHT(B7,1)+1

Just another way of getting the row reference for where the results are (i.e. one row down from where the pupil's name is)

What's the result?

=INDEX(INDIRECT(E7),1,D7)

we now know the row we want & the column we want, so we can stick these values in an index function to return the final result.

This could all be done with a lot less typing, but like I said I thought I'd make it explicit.


Post back if you need further explanations.

paddy
 
Upvote 0
thanks for the explenation Paddy

ps i didnt set up the spreadsheet, its what i got from the exam board, i am just trying to extract the data for my subject, i teach highschool maths, then i will do the same for the other subjects

john
 
Upvote 0
ps i didnt set up the spreadsheet, its what i got from the exam board

In which case hunt them down & kill them. No-one who does that sort of thing to a speadsheet has any right to be judging anyone!
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top