Confused about what function to use

[babyGrL]

hi there,
i have a problem i hope someone can solve:

i have two lists, that i need to merge into one report that lists
all stock with their qty on hand then their total annual sales movement.
i have all the information i need in two separate lists:

1. Qty on hand of all stock (which list everything in stock even where qty=0)
2. Sales movement of stock (which only lists the item if its sales are greater than 0)

the qty list is always larger than the sales list.

i am frequently asked by my superiors to create reports
where there is a full listing of all items in stock with their qty, then next to each
qty figure, put in the sales movement figure.

this would be easy if the qty list was shorter than the sales list,
because then i could just use the Lookup function.

as it is now, i dont know what to do. is there another function in excel
that i can use? if not what other options do i have?

all suggestions are very much appreciated.
thank you in advance.

 

[SydneyGeek]

How about VLOOKUP?

Denis

 

[shemayisroel]

How about VLOOKUP?

Denis

Agree, see below for some good working examples

http://www.contextures.com/xlFunctions02.html

 

[babyGrL]

i have tried using VLookup, but even when there is a matching item code in both lists, the results of the function comes back as #N/A for all items in the list.

 

[shemayisroel]

Hi,

Could you please post some sample data including the formula you are using and I'll take a look at it for you.

Use excel jeanie to post your sample data

http://www.excel-jeanie-html.de/index.php?f=1

If not, then please post a copy of your formula.

 

[babyGrL]

OK, here it is

=VLOOKUP(A6,Sale!A3:B1140,2,0)

A6 is the top most cell in the column that consists of the stock codes
The Sale worksheet has the array,
1st column consists stock codes and
2nd column has the corresponding sales movement figure for that code
- this sales movement figure is what i need to be returned.

The range-lookup is false to find an exact match

is this enough info?
i really appreciate your time

 

[shemayisroel]

Ok the formula and data layout seem fine, then there must be something within your data thats throwing it out

Are the stock codes in text? In either in A6 or in the sales worksheet?

In your sales worksheet, is the data copied from a database? If so then there is a chance that the formatting is out (spaces before and or after the stock codes) or set to text, please confirm...

 

[SydneyGeek]

I'd make the range an absolute reference, or else the reference will change as you copy the formula down:

=VLOOKUP($A6,Sale!$A$3:$B$1140,2,0)

You don't strictly need the $A6 unless you are copying across, but I do it as a habit so I don't get caught.
Note: If any items are not in the Sales sheet you will get NA errors.

Denis

 

[babyGrL]

shemayisroel, yes i've made sure that the cells are formatted as number, and both lists are sorted in ascending order.
SydneyGeek, i've just tried your suggestion to use absolute reference and its works GREAT! its returning the correct sales figure wherever a match is found and #N/A when there is no match found.
Thank you both so much for your time and assistance! it will save me a great deal of time now.
i owe you both a beer!

cheers

 

[shemayisroel]

Your welcome, if you wanted to replace the error with something like "Not Found" you can use the below formula

=IF(ISNA(VLOOKUP($A6,Sale!$A$3:$B$1140,2,0)),"Not Found",VLOOKUP($A6,Sale!$A$3:$B$1140,2,0))