I hate to state the obvious but if the array 'Products' is not sorted in
ascending order it won't work. It has to be sorted for the column you are
looking up - not the column you are returning data from.
The difference between using 'FALSE' or 'TRUE' is simply when the item being
looked up can't be found does it return 'N/A' or return the value of the
'next record'.
Also it's easy to use an external reference. Simply replace 'product' with
an external reference to an array in another spreadsheet (assume the
spreadsheet is in the same directory on the same computer).
Leave lots of blank rows in the array at the end so you can add new products
and resort the array. The other spreadsheets when they are opened will
update the external references.
see:
http://205.209.16.190/excel/vlookup.jpg
"Cal" wrote:
Hello,
I have just learned about the VLOOKUP function recently and think that
it might be what I need to use to make a certain task much much
easier.
What I have now is a master list (sort of like a key) it tells me
types of products that our clients own. These different products are
grouped by a number. For example:
Tide Laundry Soap might be a 1 because it is a soap.
Purex Laundry Soap would also be a 1
While Paper plates would be a 2 for consumable (for example).
Here is a pictu
http://www.jungleduck.com/pictures/excelexample.jpg
I have 6 groups numbered 1-5 and one group is labled with a blank
cell.
What I usually do is copy & paste the client's info into Excel
& then I type 1,2,3,4,5 or ___ next to each product, then I sort
it and then I copy that data into a "pretty" spreadsheet that is
organized for my boss to calculate some information about the client.
What is very time consuming is this "coding" part. I wanted to use IF
statements to put the numbers into the cells for me. I found out that
I cannot use more than 7/8 items in a nested IF, since I have over a
1000 products to list VLOOKUP seemed to make the most sence.
I understand how it works, but I cannot get it TO work.
I believe my function looked like =VLOOKUP(C3,Products,2,TRUE)
Because I wanted the data in C3 (and C4...C5 respectively) to be coded
according to column 2 in my ranged data table.
However when I use the ranged data and put the function into D3 (the
Code column) it gives me "N/A." The ranged data is in the next sheet
over but I can't get it to use it for some reason.
What am I doing wrong & is there an easier way to get this coding
done? It is very time consuming and of course vulnerable to user
error. I wanted to make some sort of formula or macro so I can simply
push a button and get the coding part done isntantly.
Also, my last question is can I somehow link the VLOOKUP to a
different workbook? Then I can just update my master list instead of
having to open 100s of different client workbooks to add in a new
product.
I hope this wasn't too confusing, it seems like it should work, I just
can't get it to work. Thank you in advance, esp if you read this all!
:)