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!
:)