Vlookup - changing array

[lipstick]

This is a bit embarrassing as I'm sure I've found the solution to this before, but can't remember how I did it:
Anyway, I have a table with

School a --- Subject 1 --- Score
School b --- Subject 1 --- Score
School c --- Subject 1 --- Score
School a --- Subject 2 --- Score
School d --- Subject 2 --- Score
School e --- Subject 2 --- Score
etc

I need to insert the 'score' values into another table using a vlookup. How can I do this using the School as the lookup value ONLY IF the subject matches? The table is ordered in subject order, so I could go down pasting vlookups with different arrays pointing it to only the scores and schools for that subject. But I know there's a better way of getting the array to change based on the subject number.

Thanks

 

[Andrew Poulsom]

If there is only one score per school/subject combination, you can use a formula like:

=SUMPRODUCT(--($A$1:$A$100="School a"),--($B$1:$B$100="Subject 1"),$C$1:$C$100)

The text entries "School a" and "Subject 1" can be cell references.

 

[jeffreybrown]

Hi Lipstick,

I'm not sure you can get that done with a vlookup, but here are a couple of other options. Since you didn't mention how your data is laid out I just came up with this.

Excel Workbook
	E	F	G	H
1	School a	Subject 1	77	77
2	School a	Subject 2	21	21
3	School b	Subject 1	91	91
4	School c	Subject 1	10	10
5	School d	Subject 2	83	83
6	School e	Subject 2	76	76
Sheet4