HOW TO EXTRACT DATA FROM A TABLE

[BAS872]

I have 14 kids in a classroom and I am building a table to sort test scores and other data for each one of them. I have tried several combinations of FindInOrder to do what I describe below and none have worked:

-- Of the three children whose number (from 1 to 14) appears in cells D12:F12,

-- Place in cell D13 the the highest test X score from table DQ3:EW16 where:

-- DQ3:DQ16 lists the student's number (from 1 to 14 in ascending order) and


-- ED3:ED16 lists the score each student obtained in the test X.

I need to do that for THREE OTHER tests whose scores are in ED3:ED16, EL3:EL16 AND EL3:EL16

Could anyone give me a formula that will accomplish that? I would really appreciate it.

Thanks

 

[ChrisUK]

Bas,

Your question is very confusing but I've put a few thoughts below which may help you.

Firstly, your table in DQ3:EW16 which starts with the student name. I would add a column to this which is =Max(DR3:EW3). This will give you the highest test score for each student.

Secondly, use a lookup function in cells D13,E13 and F13 to give you the best 3 score for each student.

=VLOOKUP(D12,StudentDetails,33,TRUE)
=VLOOKUP(E12,StudentDetails,33,TRUE)
=VLOOKUP(F12,StudentDetails,33,TRUE)

You will notice I've defined the table name as a range name. 33 is the offset in the table to the nex column.

To get the results from a specific test for each of your students use the lookup once again but this time the offset will need changing to be that of the test column.

I hope this helps you.

 

[royUK]

I think you need to look at how you have set up your data. If you list everthing in separate columns perhaps Studentnane;Test;Score you can then use a PivotTable and/or AutoFilter to extract your data.I can email an example if you want
This message was edited by royUK on 2002-10-01 06:10

 

[PaddyD]

This sort of thing??

Book1
	A	B	C	D	E	F
1	Data
2		1	2	3	4	5
3	testa	10	30	80	70	90
4	testb	50	30	65	65	65
5
6
7	student	1	3	4
8	Maxa	80
9	Maxb	65
10
Sheet2

i.e.
=MAX(HLOOKUP(B7,B2:F4,2,0),HLOOKUP(C7,B2:F4,2,0),HLOOKUP(D7,B2:F4,2,0))

just using straightforward hlookups to get the relevant values & sticking the results in a max? Sure it could be shortened / arrayed, but it's late & I refuse toi think anymore.

CHange the references to suit...

Paddy