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.

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.

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.