Look for value and subtract if duplicated

Question

I have an Excel table as follows:

Color    | Inventory
Blue     |  180
Red      |   50
Green    |   10

I want to look up the inventory of the color that I would require depending which product I'm going to use and then subtract if it was used on a past line, my table would look like this:

Item   | Color | Usage | Inventory Left
ABC    | Blue  |  50   |  130
BCD    | Blue  |  30   |  100
CDE    | Blue  |  50   |   50
EFG    | Red   |  25   |   25
FGH    | Red   |  10   |   15
GHI    | Green |   5   |    5

How could I do it with an Excel formula?

I was thinking in some type of SUMIF with OFFSET but I can't figure it out.

Answer 1

With a layout as shown, please try:

=VLOOKUP(B9,$A$1:$B$6,2,0)-SUMIF(B$1:B9,B9,C$1:C9)

The principle would be the same but a different layout might be more convenient.