Looking up with more than 1 criteria

[Fiona3300]

I have a worksheet which has lists of test results, the sheet has been
generated automatically. Unfortunately the way the sheet is layed out is not
particularly useful!
A student may have sat more than one test in different subjects. Each of
these results is on a separate row. e.g. (this is a simplified version -
there are hundreds of names many of which are repeated under different
subject test results)
John Smith Maths D
Jane Brown English C
John Smith English C
Jane Brown Science B
What I would like to do is to transfer the grades to another sheet in the
workbook so it looks like this;
Name Maths English Science
John Smith D C
Jane Brown C B.
So in each of the cells showing the grades I would like a formula that would
search the other sheet, looking for name and subject and the result of the
formula would find the grade! Can I find anything that will do this? - not so
far so hence I'm letting all you experts out there to have a go!
I have found one formula in the forum which involves using match functions
nested within an index formula. I tried to adapt it to my needs but either it
didn't work or I did it wrong (which is probably more likely). Any help or
ideas will be greatly appreciated.

 

[muddan madhu]

suppose u data in sheet 1

Col A Names
Col B Subject
Col C Result

Sheet 2 in B1 put Maths, C1 = English & D1 = Science

In Col A u have names ......

in B2 put this formula =INDEX(Sheet1!$C:$C,MATCH(A2,Sheet1!$A$1:$A
$100,0)*MATCH(B$1,Sheet1!$B$1:$B$100,0),0) & drag it down

in C2 put this formula =INDEX(Sheet1!$C:$C,MATCH(A2,Sheet1!$A$1:$A
$100,0)*MATCH(C$1,Sheet4!$B$1:$B$100,0),0) & drag it down

in D2 put this formula =INDEX(Sheet1!$C:$C,MATCH(A2,Sheet1!$A$1:$A
$100,0)*MATCH(D$1,Sheet1!$B$1:$B$100,0),0) & drag it down

 

[Fiona3300]

HI
Many thanks for this, I set up a sheet exactly as you described so I can
what would happen. Trouble is, it's returning 0 for some results even though
there is a match in the other sheet. There is one student who got an A in a
subject test but in the sheet it's returned as a 0. Any ideas on this bit?
Also I have around 300 results and I tried altering the formula to account
for this $A$1:$A$300 instead of $A$1:$A$100 but it just displayed the formula
as text.
Sorry if I am being a bit thick!

 

[Max]

Another play to try ..

Source data assumed in Sheet1, cols A to C, data from row2 down
Col A = Student names, col B = Subjects, col C = Grades

In Sheet2,
Student names are assumed listed in A2 down, Subjects in B1 across
Put in B2, normal ENTER
=IF(ISNA(MATCH(1,INDEX((TRIM(Sheet1!$A$2:$A$100)=TRIM($A2))*(TRIM(Sheet1!$B$2:$B$100)=TRIM(B$1)),),0)),"",INDEX(Sheet1!$C$2:$C$100,MATCH(1,INDEX((TRIM(Sheet1!$A$2:$A$100)=TRIM($A2))*(TRIM(Sheet1!$B$2:$B$100)=TRIM(B$1)),),0)))
Copy B2 across/fill down to populate. This should return better results with
the use of TRIM around both the lookup ranges/values to remove any extraneous
white spaces which may be throwing apparent matches off. Adapt the ranges to
suit.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:62
xdemechanik
---

 

[Fiona3300]

Fantastic!! Worked a treat - no idea how but it works!
Many thanks for your help.

Fiona

 

[Max]

Welcome, but pl take a moment to press the "Yes" buttons below, from where
you're reading/posting.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:62
xdemechanik