Formula to look up a column and paste results in another column
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! |
Formula to look up a column and paste results in another column
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 |
Formula to look up a column and paste results in another column
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" wrote: 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! |
Formula to look up a column and paste results in another colum
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! "vezerid" wrote: 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 |
Formula to look up a column and paste results in another colum
If your list only has "Dog" or "Dog1" then try:
=IF(OR(D3="Dog",D3="Dog1"),($F$2-COUNTIF($D$3:D3,"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) "DM" wrote: 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! "vezerid" wrote: 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 |
All times are GMT +1. The time now is 07:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com