# HOW TO EXTRACT DATA FROM A TABLE

#### BAS872

##### New Member
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

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Bas,

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.

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

This sort of thing??
Book1
ABCDEF
1Data
212345
3testa1030807090
4testb5030656565
5
6
7student134
8Maxa80
9Maxb65
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...

Replies
1
Views
145
Replies
1
Views
270
Replies
4
Views
692
Replies
5
Views
466
Replies
3
Views
527

1,221,497
Messages
6,160,150
Members
451,626
Latest member
sukhman

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