# lookup?

#### johnpdavey

##### Board Regular
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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Try this formulas:
Libro6.xls
ABCDEFGH
2JOEMaEngSciPeHiGeArt
312ACGUEEF
4BOBEngSciMa
56EFU
6
7
8
9JOEA
10BOBU
Hoja3

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

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

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:

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.

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

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!

Replies
1
Views
399
Replies
2
Views
191
Replies
16
Views
1K
Replies
2
Views
122
Replies
1
Views
243

1,221,418
Messages
6,159,793
Members
451,589
Latest member
Harold14

### 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.

### Which adblocker are you using?

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

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