Dual-Conditional Subtraction Formula

[jahwohl]

EDIT: nevermind, forgot to insert it as an array function by hitting CTRL-SHIFT-ENTER. Whoops!

I have an Excel 2010 spreadsheet with the following fields:

Name - Text field
Type - Text field containing one of {A,B,C,D,E}
Score - numeric score (worksheet is sorted in descending order on this field)
Available - Text field containing one of {yes,no}

The field I am trying to compute is Delta, which is the difference between an item's score and the score of the next available item of the same type.

Right now I am able to compute the Delta field without considering availability by using the following formula:

=B2-INDEX(B3:B6,MATCH(C2,C3:C6,0))

where B is the Score column and C is the Type column.

What I'm looking for is something like:

=B2-INDEX(B3:B6,MATCH(C2,IF(D3:D6="yes",C3:C6),0))

(where D is the Available column.)

However, this doesn't seem to work. Any ideas?

(I've attached a sample workbook with a snippet of data.)