VLOOKUP Function using Data Ranges.

[Cal]

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!

[Niek Otten]

Try

=VLOOKUP(C3,Products,2,FALSE)

Post again if this wasn't the problem

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel


"Cal" <[email protected]> wrote in message
news:[email protected]...
> 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!
>
>

[Biff]

Hi!

This sounds like something that I would have to "eye-ball"
but you can try this and see if it makes a difference:

>=VLOOKUP(C3,Products,2,TRUE)

Change to:

=VLOOKUP(C3,Products,2,FALSE)

Are you sure that "Products" points to the correct place?

Biff

>-----Original Message-----
>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!
>
>
>.
>

[OVERLOAD]

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

[Cal]

Thank you guys SO much for your help. I managed to get it to work..
almost.
I have 2 questions more

First one is, I have a bunch of different items that have a percent in
their name. Even though they have different names is there anyway to
make it so when it sees a percent sign (%) it codes it always the
same. Should I just do a % with a code in my Products range and then
change VLOOKUP to "true"?

Second question is, I have some items that are coded with a blank cell
because there are so many different kinds. The VLOOKUP puts a 0 in
instead of a blank cell. This is not a big deal but is there anyway
to make it put a blank cell instead?

THANK YOU so much for your help, I must be truelly a computer geek as
this breakthru has made my weekend! x5

[CLR]

For the second question, wrap your VLOOKUP formula in an IF
statement...........like:

=IF(VLOOKUP(A3,I1:J4,2,FALSE)=0,"",VLOOKUP(A3,I1:J4,2,FALSE))

Vaya con Dios,
Chuck, CABGx3


"Cal" <[email protected]> wrote in message
news:[email protected]...
> Thank you guys SO much for your help. I managed to get it to work..
> almost.
> I have 2 questions more
>
> First one is, I have a bunch of different items that have a percent in
> their name. Even though they have different names is there anyway to
> make it so when it sees a percent sign (%) it codes it always the
> same. Should I just do a % with a code in my Products range and then
> change VLOOKUP to "true"?
>
> Second question is, I have some items that are coded with a blank cell
> because there are so many different kinds. The VLOOKUP puts a 0 in
> instead of a blank cell. This is not a big deal but is there anyway
> to make it put a blank cell instead?
>
> THANK YOU so much for your help, I must be truelly a computer geek as
> this breakthru has made my weekend! x5
>

[OVERLOAD]

Both are easy.
1. Assuming the "%" is anywhere within your text string simply replace the
cell reference in the lookup table with a formula which the result is either
the cell or "%", then 'lookup' that.
=VLOOKUP(IF(ISERROR(FIND("%",A2)),A2,"%"),$D$2:$E$23,2,FALSE)
then for any value with a '%' anywhere in the text the lookup will look for
"%" instead of A2. MAKE SURE YOU HAVE A "%" in your lookup table.

2. Instead of leaving the return value in your lookup table 'blank' - or a
'null' value (ie. nothing in the cell), put a 'space' in the cell. It will
now be a specific character - although it will look like an empty cell.



"Cal" wrote:

> Thank you guys SO much for your help. I managed to get it to work..
> almost.
> I have 2 questions more
>
> First one is, I have a bunch of different items that have a percent in
> their name. Even though they have different names is there anyway to
> make it so when it sees a percent sign (%) it codes it always the
> same. Should I just do a % with a code in my Products range and then
> change VLOOKUP to "true"?
>
> Second question is, I have some items that are coded with a blank cell
> because there are so many different kinds. The VLOOKUP puts a 0 in
> instead of a blank cell. This is not a big deal but is there anyway
> to make it put a blank cell instead?
>
> THANK YOU so much for your help, I must be truelly a computer geek as
> this breakthru has made my weekend! x5
>
>