help with vlookup formula

[Guest]

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.

 

[Daniel CHEN]

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
==================================

 

[Trevor Shuttleworth]

Try changing the TRUE to FALSE

Regards

Trevor

 

[Dave Peterson]

One more:

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

 

[Guest]

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

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

--
Best regards,
---
Yongjun CHEN
==================================
- - - - www.XLDataSoft.com - - - -
Free Tool & Training Material for Download
==================================

 

[Guest]

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

 

[Guest]

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?

 

[Guest]

Dave, that one gives a #REF error.

One more:

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

 

[Guest]

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

 

[Pete_UK]

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

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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.

 

[Dave Peterson]

I bet you made a typing mistake.

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?

 

[Guest]

I think i found my way around that problem

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.