Formula to look up a column and paste results in another column

[DM]

I've tried to use Vlookup for this but was unable to get it working.
Here's what I need:

In column 3 I have:

dog
dog1
cat
cat2

I'm trying to get an inventory management tool by looking at column 3 and
subtracting 1 from the previous row, so I would have:

Column 3 Column 4 for dog Column 5 for dog1...
100
dog 99
dog1 0
cat 0
cat2 0
dog 98

So, if I have "dog" in row 3 then I get a formula to look up "dog" then
subtract 1 from the previous row (or previous valid number, maybe not 0) and
give me the result, and if it is not "dog" it'd show a 0.

Hope I was clear enough... PLEASE HELP!

 

[vezerid]

I am assuming 100 is in D2 and 99 is in D3. Then the formula for D3 and
down is:

=$D$2-COUNTIF($C$3:C3, "dog")

Alternatively, if you have the words dog, dog1, cat, cat1 etc in D1, E1
etc, you can use the following formula in D3 and copy accross and down:

=$D$2-COUNTIF($C$3:C3, D$1)

HTH
Kostis Vezerides

 

[jeffstew1]

If having a zero (0) was not important, you could set it up like this:

Criteria Formula Result
100 100
dog =IF(A3="Dog",B2-1,0) 99
Dog1 =IF(A4="Dog",MIN($B$2:B3)-1,"0") 0
Cat =IF(A5="Dog",MIN($B$2:B4)-1,"0") 0
Cat2 =IF(A6="Dog",MIN($B$2:B5)-1,"0") 0
dog =IF(A7="Dog",MIN($B$2:B6)-1,"0") 98

 

[DM]

Is it possible to add a second variable to the formula to also look for
"dog1" at the same time? How do I do that? Something like
=$D$2-COUNTIF($C$3:C3, "dog"; "dog1")

Thank you!

 

[jeffstew1]

If your list only has "Dog" or "Dog1" then try:

=IF(OR(D3="Dog",D3="Dog1"),($F$2-COUNTIF($D$33,"dog*")),0)

Otherwise is you have more types of "Dog" then use:

=IF(OR(A3="Dog",A3="Dog1"),($B$2-(COUNTIF($A$3:A3,"dog")+COUNTIF($A$3:A3,"dog1"))),0)