help with vlookup formula
 

Hi,
I have two worksheets, an invoice, and an inventory. What i need is to be
able to deduct quantity sold (from the invoice) from the quantity in stock
(in the inventory). I tried using a vlookup formula to get the product code
from the invoice and subtract the quantity sold from an amount bought and
recorded in the invetory list. The formula is as follows:
=H6-(VLOOKUP(B6,Invoice!$A$16:$B$32,1,TRUE)). However, this only reduces the
amount by 3, whether or not the amount sold is 3. Does anyone know what i'm
doing wrong? Please, any help is appreciated.

Column H - has the anount of goods bought and is available for sale.
Column B - has the unique probuct code.

In the invoice sheet,
Column A - has the quantity sold
Column B - has the code

Thank You.

help with vlookup formula
 

VLOOKUP only looks up value from the first column.
For your case, you value (code) is in the second column (B), so you use of
vlookup is not right.
Try to use

=H6-INDEX(Invoice!$A$16:$B$32,MATCH(B6,Invoice!$A$16:$ B$32,0))

--
Best regards,
---
Yongjun CHEN
==================================
- - - - www.XLDataSoft.com - - - -
Free Tool & Training Material for Download
==================================
"Paula_p" wrote in message
...
Hi,
I have two worksheets, an invoice, and an inventory. What i need is to be
able to deduct quantity sold (from the invoice) from the quantity in stock
(in the inventory). I tried using a vlookup formula to get the product
code
from the invoice and subtract the quantity sold from an amount bought and
recorded in the invetory list. The formula is as follows:
=H6-(VLOOKUP(B6,Invoice!$A$16:$B$32,1,TRUE)). However, this only reduces
the
amount by 3, whether or not the amount sold is 3. Does anyone know what
i'm
doing wrong? Please, any help is appreciated.

Column H - has the anount of goods bought and is available for sale.
Column B - has the unique probuct code.

In the invoice sheet,
Column A - has the quantity sold
Column B - has the code

Thank You.

help with vlookup formula
 

Try changing the TRUE to FALSE

Regards

Trevor


"Paula_p" wrote in message
...
Hi,
I have two worksheets, an invoice, and an inventory. What i need is to be
able to deduct quantity sold (from the invoice) from the quantity in stock
(in the inventory). I tried using a vlookup formula to get the product
code
from the invoice and subtract the quantity sold from an amount bought and
recorded in the invetory list. The formula is as follows:
=H6-(VLOOKUP(B6,Invoice!$A$16:$B$32,1,TRUE)). However, this only reduces
the
amount by 3, whether or not the amount sold is 3. Does anyone know what
i'm
doing wrong? Please, any help is appreciated.

Column H - has the anount of goods bought and is available for sale.
Column B - has the unique probuct code.

In the invoice sheet,
Column A - has the quantity sold
Column B - has the code

Thank You.

help with vlookup formula
 

One mo

=H6-INDEX(Invoice!$A$16:$A$32,MATCH(B6,Invoice!$b$16:$ B$32,0))



Paula_p wrote:

Hi,
I have two worksheets, an invoice, and an inventory. What i need is to be
able to deduct quantity sold (from the invoice) from the quantity in stock
(in the inventory). I tried using a vlookup formula to get the product code
from the invoice and subtract the quantity sold from an amount bought and
recorded in the invetory list. The formula is as follows:
=H6-(VLOOKUP(B6,Invoice!$A$16:$B$32,1,TRUE)). However, this only reduces the
amount by 3, whether or not the amount sold is 3. Does anyone know what i'm
doing wrong? Please, any help is appreciated.

Column H - has the anount of goods bought and is available for sale.
Column B - has the unique probuct code.

In the invoice sheet,
Column A - has the quantity sold
Column B - has the code

Thank You.

--

Dave Peterson

help with vlookup formula
 

I tried that formula but i get a #N/A error.

"Daniel CHEN" wrote:

VLOOKUP only looks up value from the first column.
For your case, you value (code) is in the second column (B), so you use of
vlookup is not right.
Try to use
=H6-INDEX(Invoice!$A$16:$B$32,MATCH(B6,Invoice!$A$16:$ B$32,0))

--
Best regards,
---
Yongjun CHEN
==================================
- - - - www.XLDataSoft.com - - - -
Free Tool & Training Material for Download
==================================
"Paula_p" wrote in message
...
Hi,
I have two worksheets, an invoice, and an inventory. What i need is to be
able to deduct quantity sold (from the invoice) from the quantity in stock
(in the inventory). I tried using a vlookup formula to get the product
code
from the invoice and subtract the quantity sold from an amount bought and
recorded in the invetory list. The formula is as follows:
=H6-(VLOOKUP(B6,Invoice!$A$16:$B$32,1,TRUE)). However, this only reduces
the
amount by 3, whether or not the amount sold is 3. Does anyone know what
i'm
doing wrong? Please, any help is appreciated.

Column H - has the anount of goods bought and is available for sale.
Column B - has the unique probuct code.

In the invoice sheet,
Column A - has the quantity sold
Column B - has the code

Thank You.

help with vlookup formula
 

I tried that before and i got a #N/A error. I tried it again, and it still
give the same error.

"Trevor Shuttleworth" wrote:

Try changing the TRUE to FALSE

Regards

Trevor


"Paula_p" wrote in message
...
Hi,
I have two worksheets, an invoice, and an inventory. What i need is to be
able to deduct quantity sold (from the invoice) from the quantity in stock
(in the inventory). I tried using a vlookup formula to get the product
code
from the invoice and subtract the quantity sold from an amount bought and
recorded in the invetory list. The formula is as follows:
=H6-(VLOOKUP(B6,Invoice!$A$16:$B$32,1,TRUE)). However, this only reduces
the
amount by 3, whether or not the amount sold is 3. Does anyone know what
i'm
doing wrong? Please, any help is appreciated.

Column H - has the anount of goods bought and is available for sale.
Column B - has the unique probuct code.

In the invoice sheet,
Column A - has the quantity sold
Column B - has the code

Thank You.

help with vlookup formula
 

Dave, That formula gives a #REF error.

After looking at all the all the help that were being suggested, i realised
that i didn't know what match and index does, so after some research i tried
tracing the formulas i was given, and i came up with this;

MATCH, takes a value, a range to be searched, and returns the position of
the value.

INDEX, takes a range, a row number, and a column number, and the result is
the actual value in the intersection.

The value that match returns, is it used as a row number or column number?
And since index requires two values, is one value missing from the formula,
or am i reading and tracing it wrong?


"Dave Peterson" wrote:

One mo

=H6-INDEX(Invoice!$A$16:$A$32,MATCH(B6,Invoice!$b$16:$ B$32,0))



Paula_p wrote:

Hi,
I have two worksheets, an invoice, and an inventory. What i need is to be
able to deduct quantity sold (from the invoice) from the quantity in stock
(in the inventory). I tried using a vlookup formula to get the product code
from the invoice and subtract the quantity sold from an amount bought and
recorded in the invetory list. The formula is as follows:
=H6-(VLOOKUP(B6,Invoice!$A$16:$B$32,1,TRUE)). However, this only reduces the
amount by 3, whether or not the amount sold is 3. Does anyone know what i'm
doing wrong? Please, any help is appreciated.

Column H - has the anount of goods bought and is available for sale.
Column B - has the unique probuct code.

In the invoice sheet,
Column A - has the quantity sold
Column B - has the code

Thank You.

--

Dave Peterson

help with vlookup formula
 

Dave, that one gives a #REF error.

"Dave Peterson" wrote:

One mo

=H6-INDEX(Invoice!$A$16:$A$32,MATCH(B6,Invoice!$b$16:$ B$32,0))



Paula_p wrote:

Hi,
I have two worksheets, an invoice, and an inventory. What i need is to be
able to deduct quantity sold (from the invoice) from the quantity in stock
(in the inventory). I tried using a vlookup formula to get the product code
from the invoice and subtract the quantity sold from an amount bought and
recorded in the invetory list. The formula is as follows:
=H6-(VLOOKUP(B6,Invoice!$A$16:$B$32,1,TRUE)). However, this only reduces the
amount by 3, whether or not the amount sold is 3. Does anyone know what i'm
doing wrong? Please, any help is appreciated.

Column H - has the anount of goods bought and is available for sale.
Column B - has the unique probuct code.

In the invoice sheet,
Column A - has the quantity sold
Column B - has the code

Thank You.

--

Dave Peterson

help with vlookup formula
 

Paula,

One important note is that the code column must be the left most column in
the table array range, meaning the code column in the invoice sheet must be
moved to the left.
So move the column in invoice sheet so that
Column A - has the code
Column B - has the quantity sold
Then try
=H6-(VLOOKUP(B6,Invoice!$A$16:$B$32,2,FALSE))
This is basically the formula you used originally, except the column index
has changed from 1 to 2, and last argument changed from TRUE to FALSE.

-Simon

"Paula_p" wrote:

Hi,
I have two worksheets, an invoice, and an inventory. What i need is to be
able to deduct quantity sold (from the invoice) from the quantity in stock
(in the inventory). I tried using a vlookup formula to get the product code
from the invoice and subtract the quantity sold from an amount bought and
recorded in the invetory list. The formula is as follows:
=H6-(VLOOKUP(B6,Invoice!$A$16:$B$32,1,TRUE)). However, this only reduces the
amount by 3, whether or not the amount sold is 3. Does anyone know what i'm
doing wrong? Please, any help is appreciated.

Column H - has the anount of goods bought and is available for sale.
Column B - has the unique probuct code.

In the invoice sheet,
Column A - has the quantity sold
Column B - has the code

Thank You.

help with vlookup formula
 

Yes, put a comma and 1 between the two brackets at the end of Dave's
formula:

=H6-INDEX(Invoice!$A$16:$A$32,MATCH(B6,Invoice!$b$16:$ B$32,0),1)

This is equivalent to your earlier formula with VLOOKUP, which when
corrected should have been:

=H6-VLOOKUP(B6,Invoice!$A$16:$B$32,2,FALSE)

Hope this helps.

Pete

Paula_p wrote:
Dave, That formula gives a #REF error.

After looking at all the all the help that were being suggested, i realised
that i didn't know what match and index does, so after some research i tried
tracing the formulas i was given, and i came up with this;

MATCH, takes a value, a range to be searched, and returns the position of
the value.

INDEX, takes a range, a row number, and a column number, and the result is
the actual value in the intersection.

The value that match returns, is it used as a row number or column number?
And since index requires two values, is one value missing from the formula,
or am i reading and tracing it wrong?


"Dave Peterson" wrote:

One mo

=H6-INDEX(Invoice!$A$16:$A$32,MATCH(B6,Invoice!$b$16:$ B$32,0))



Paula_p wrote:

Hi,
I have two worksheets, an invoice, and an inventory. What i need is to be
able to deduct quantity sold (from the invoice) from the quantity in stock
(in the inventory). I tried using a vlookup formula to get the product code
from the invoice and subtract the quantity sold from an amount bought and
recorded in the invetory list. The formula is as follows:
=H6-(VLOOKUP(B6,Invoice!$A$16:$B$32,1,TRUE)). However, this only reduces the
amount by 3, whether or not the amount sold is 3. Does anyone know what i'm
doing wrong? Please, any help is appreciated.

Column H - has the anount of goods bought and is available for sale.
Column B - has the unique probuct code.

In the invoice sheet,
Column A - has the quantity sold
Column B - has the code

Thank You.

--

Dave Peterson

help with vlookup formula
 

Since the range being used for INDEX is one dimension, you don't need both
row and column arguments. You only need both when using a two dimensional
range.



"Pete_UK" wrote:

Yes, put a comma and 1 between the two brackets at the end of Dave's
formula:

=H6-INDEX(Invoice!$A$16:$A$32,MATCH(B6,Invoice!$b$16:$ B$32,0),1)

This is equivalent to your earlier formula with VLOOKUP, which when
corrected should have been:

=H6-VLOOKUP(B6,Invoice!$A$16:$B$32,2,FALSE)

Hope this helps.

Pete

Paula_p wrote:
Dave, That formula gives a #REF error.

After looking at all the all the help that were being suggested, i realised
that i didn't know what match and index does, so after some research i tried
tracing the formulas i was given, and i came up with this;

MATCH, takes a value, a range to be searched, and returns the position of
the value.

INDEX, takes a range, a row number, and a column number, and the result is
the actual value in the intersection.

The value that match returns, is it used as a row number or column number?
And since index requires two values, is one value missing from the formula,
or am i reading and tracing it wrong?


"Dave Peterson" wrote:

One mo

=H6-INDEX(Invoice!$A$16:$A$32,MATCH(B6,Invoice!$b$16:$ B$32,0))



Paula_p wrote:

Hi,
I have two worksheets, an invoice, and an inventory. What i need is to be
able to deduct quantity sold (from the invoice) from the quantity in stock
(in the inventory). I tried using a vlookup formula to get the product code
from the invoice and subtract the quantity sold from an amount bought and
recorded in the invetory list. The formula is as follows:
=H6-(VLOOKUP(B6,Invoice!$A$16:$B$32,1,TRUE)). However, this only reduces the
amount by 3, whether or not the amount sold is 3. Does anyone know what i'm
doing wrong? Please, any help is appreciated.

Column H - has the anount of goods bought and is available for sale.
Column B - has the unique probuct code.

In the invoice sheet,
Column A - has the quantity sold
Column B - has the code

Thank You.

--

Dave Peterson

help with vlookup formula
 

Simon, everything is working just fine now.
Thank you for your help, it is greatly appreciated.

"SiC" wrote:

Paula,

One important note is that the code column must be the left most column in
the table array range, meaning the code column in the invoice sheet must be
moved to the left.
So move the column in invoice sheet so that
Column A - has the code
Column B - has the quantity sold
Then try
=H6-(VLOOKUP(B6,Invoice!$A$16:$B$32,2,FALSE))
This is basically the formula you used originally, except the column index
has changed from 1 to 2, and last argument changed from TRUE to FALSE.

-Simon

"Paula_p" wrote:

Hi,
I have two worksheets, an invoice, and an inventory. What i need is to be
able to deduct quantity sold (from the invoice) from the quantity in stock
(in the inventory). I tried using a vlookup formula to get the product code
from the invoice and subtract the quantity sold from an amount bought and
recorded in the invetory list. The formula is as follows:
=H6-(VLOOKUP(B6,Invoice!$A$16:$B$32,1,TRUE)). However, this only reduces the
amount by 3, whether or not the amount sold is 3. Does anyone know what i'm
doing wrong? Please, any help is appreciated.

Column H - has the anount of goods bought and is available for sale.
Column B - has the unique probuct code.

In the invoice sheet,
Column A - has the quantity sold
Column B - has the code

Thank You.

help with vlookup formula
 

I have another problem,
When the cell is empty, #N/A is displayed in these cells. I've tried to
correct this with the following;
=if(isna(H3-(VLOOKUP(B3,Invoice!$A$16:$B$32,2,FALSE)),"",H3-(VLOOKUP(B3,Invoice!$A$16:$B$32,2,FALSE)))), but i keep getting an excel warning error.


"Paula_p" wrote:

Simon, everything is working just fine now.
Thank you for your help, it is greatly appreciated.

"SiC" wrote:

Paula,

One important note is that the code column must be the left most column in
the table array range, meaning the code column in the invoice sheet must be
moved to the left.
So move the column in invoice sheet so that
Column A - has the code
Column B - has the quantity sold
Then try
=H6-(VLOOKUP(B6,Invoice!$A$16:$B$32,2,FALSE))
This is basically the formula you used originally, except the column index
has changed from 1 to 2, and last argument changed from TRUE to FALSE.

-Simon

"Paula_p" wrote:

Hi,
I have two worksheets, an invoice, and an inventory. What i need is to be
able to deduct quantity sold (from the invoice) from the quantity in stock
(in the inventory). I tried using a vlookup formula to get the product code
from the invoice and subtract the quantity sold from an amount bought and
recorded in the invetory list. The formula is as follows:
=H6-(VLOOKUP(B6,Invoice!$A$16:$B$32,1,TRUE)). However, this only reduces the
amount by 3, whether or not the amount sold is 3. Does anyone know what i'm
doing wrong? Please, any help is appreciated.

Column H - has the anount of goods bought and is available for sale.
Column B - has the unique probuct code.

In the invoice sheet,
Column A - has the quantity sold
Column B - has the code

Thank You.

help with vlookup formula
 

I bet you made a typing mistake.

Paula_p wrote:

Dave, That formula gives a #REF error.

After looking at all the all the help that were being suggested, i realised
that i didn't know what match and index does, so after some research i tried
tracing the formulas i was given, and i came up with this;

MATCH, takes a value, a range to be searched, and returns the position of
the value.

INDEX, takes a range, a row number, and a column number, and the result is
the actual value in the intersection.

The value that match returns, is it used as a row number or column number?
And since index requires two values, is one value missing from the formula,
or am i reading and tracing it wrong?


"Dave Peterson" wrote:

One mo

=H6-INDEX(Invoice!$A$16:$A$32,MATCH(B6,Invoice!$b$16:$ B$32,0))



Paula_p wrote:

Hi,
I have two worksheets, an invoice, and an inventory. What i need is to be
able to deduct quantity sold (from the invoice) from the quantity in stock
(in the inventory). I tried using a vlookup formula to get the product code
from the invoice and subtract the quantity sold from an amount bought and
recorded in the invetory list. The formula is as follows:
=H6-(VLOOKUP(B6,Invoice!$A$16:$B$32,1,TRUE)). However, this only reduces the
amount by 3, whether or not the amount sold is 3. Does anyone know what i'm
doing wrong? Please, any help is appreciated.

Column H - has the anount of goods bought and is available for sale.
Column B - has the unique probuct code.

In the invoice sheet,
Column A - has the quantity sold
Column B - has the code

Thank You.

--

Dave Peterson


--

Dave Peterson

help with vlookup formula
 

I think i found my way around that problem


"Paula_p" wrote:

I have another problem,
When the cell is empty, #N/A is displayed in these cells. I've tried to
correct this with the following;
=if(isna(H3-(VLOOKUP(B3,Invoice!$A$16:$B$32,2,FALSE)),"",H3-(VLOOKUP(B3,Invoice!$A$16:$B$32,2,FALSE)))), but i keep getting an excel warning error.


"Paula_p" wrote:

Simon, everything is working just fine now.
Thank you for your help, it is greatly appreciated.

"SiC" wrote:

Paula,

One important note is that the code column must be the left most column in
the table array range, meaning the code column in the invoice sheet must be
moved to the left.
So move the column in invoice sheet so that
Column A - has the code
Column B - has the quantity sold
Then try
=H6-(VLOOKUP(B6,Invoice!$A$16:$B$32,2,FALSE))
This is basically the formula you used originally, except the column index
has changed from 1 to 2, and last argument changed from TRUE to FALSE.

-Simon

"Paula_p" wrote:

Hi,
I have two worksheets, an invoice, and an inventory. What i need is to be
able to deduct quantity sold (from the invoice) from the quantity in stock
(in the inventory). I tried using a vlookup formula to get the product code
from the invoice and subtract the quantity sold from an amount bought and
recorded in the invetory list. The formula is as follows:
=H6-(VLOOKUP(B6,Invoice!$A$16:$B$32,1,TRUE)). However, this only reduces the
amount by 3, whether or not the amount sold is 3. Does anyone know what i'm
doing wrong? Please, any help is appreciated.

Column H - has the anount of goods bought and is available for sale.
Column B - has the unique probuct code.

In the invoice sheet,
Column A - has the quantity sold
Column B - has the code

Thank You.