Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP and IF Statements
I have 2 worksheets that I am working with. Sheet 2 has all of my item
numbers and descriptions (The Database). Sheet 1 is where data is put in to get a quantity (There are multiple data inputs). Right now I enter in the item number and it gives me the description using VLOOKUP. I want to enter in the required data and the item number, and I want it to do a calculation based on the item number in the "Quantity" column. I have tried using an €œif€ statement similar to: =IF(C7=(VLOOKUP(C7,Sheet2!A1010:A1220,1,FALSE)), (((H7*I7)/9)*J7)/2000, IF(C7=(VLOOKUP(C7,Sheet2!831:850,1,FALSE)),(H7*I7)/9,IF(C7=(VLOOKUP(C7,Sheet2!815,1,FALSE)),(H7*I7)/9,"ERROR"))) I want to be able to copy this equation through the entire "Quantity" column so that whatever the item may be it will calculate the Quantity. I get a quantity if it meets the first argument, but it never looks past that to see if it is a different item number. All I get is a €œN/A€. I have tried a lot of stuff trying to get it to work, but I have had no luck. Your help is much appreciated. Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP and IF Statements
To test if a value is present in the list, don't use "IF(C7=....etc
Use IF(ISNA(YourLookupFormula.......etc -- Kind regards, Niek Otten Microsoft MVP - Excel "cranen" wrote in message ... I have 2 worksheets that I am working with. Sheet 2 has all of my item numbers and descriptions (The Database). Sheet 1 is where data is put in to get a quantity (There are multiple data inputs). Right now I enter in the item number and it gives me the description using VLOOKUP. I want to enter in the required data and the item number, and I want it to do a calculation based on the item number in the "Quantity" column. I have tried using an €œif€ statement similar to: =IF(C7=(VLOOKUP(C7,Sheet2!A1010:A1220,1,FALSE)), (((H7*I7)/9)*J7)/2000, IF(C7=(VLOOKUP(C7,Sheet2!831:850,1,FALSE)),(H7*I7)/9,IF(C7=(VLOOKUP(C7,Sheet2!815,1,FALSE)),(H7*I7)/9,"ERROR"))) I want to be able to copy this equation through the entire "Quantity" column so that whatever the item may be it will calculate the Quantity. I get a quantity if it meets the first argument, but it never looks past that to see if it is a different item number. All I get is a €œN/A€. I have tried a lot of stuff trying to get it to work, but I have had no luck. Your help is much appreciated. Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP and IF Statements
Thanks. I appreciate the help. I ended up using the "ISTEXT", but I
wouldn't have thought about it without your help. "Niek Otten" wrote: To test if a value is present in the list, don't use "IF(C7=....etc Use IF(ISNA(YourLookupFormula.......etc -- Kind regards, Niek Otten Microsoft MVP - Excel "cranen" wrote in message ... I have 2 worksheets that I am working with. Sheet 2 has all of my item numbers and descriptions (The Database). Sheet 1 is where data is put in to get a quantity (There are multiple data inputs). Right now I enter in the item number and it gives me the description using VLOOKUP. I want to enter in the required data and the item number, and I want it to do a calculation based on the item number in the "Quantity" column. I have tried using an €œif€ statement similar to: =IF(C7=(VLOOKUP(C7,Sheet2!A1010:A1220,1,FALSE)), (((H7*I7)/9)*J7)/2000, IF(C7=(VLOOKUP(C7,Sheet2!831:850,1,FALSE)),(H7*I7)/9,IF(C7=(VLOOKUP(C7,Sheet2!815,1,FALSE)),(H7*I7)/9,"ERROR"))) I want to be able to copy this equation through the entire "Quantity" column so that whatever the item may be it will calculate the Quantity. I get a quantity if it meets the first argument, but it never looks past that to see if it is a different item number. All I get is a €œN/A€. I have tried a lot of stuff trying to get it to work, but I have had no luck. Your help is much appreciated. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If statements in Vlookup - can it be done? | Excel Discussion (Misc queries) | |||
Help with Vlookup and If Statements | Excel Discussion (Misc queries) | |||
can I use =if with vlookup statements | Excel Worksheet Functions | |||
Vlookup and if statements | New Users to Excel | |||
VLOOKUP statements | Excel Discussion (Misc queries) |