RE: VLOOKUP Function using Data Ranges.
- From: "OVERLOAD" <OVERLOAD@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 14 Apr 2005 19:11:06 -0700
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 picture:
> 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!
> :)
>
>
.
- References:
- VLOOKUP Function using Data Ranges.
- From: Cal
- VLOOKUP Function using Data Ranges.
- Prev by Date: Re: How do I copy only the month from a date
- Next by Date: Re: Not Calculating?
- Previous by thread: VLOOKUP Function using Data Ranges.
- Next by thread: re:VLOOKUP Function using Data Ranges.
- Index(es):
Relevant Pages
|
