VLOOKUP Function using Data Ranges.

[Cal]

Hello
I have just learned about the VLOOKUP function recently and think tha
it might be what I need to use to make a certain task much muc
easier

What I have now is a master list (sort of like a key) it tells m
types of products that our clients own. These different products ar
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
While Paper plates would be a 2 for consumable (for example)

Here is a picture
http://www.jungleduck.com/pictures/excelexample.jp

I have 6 groups numbered 1-5 and one group is labled with a blan
cell
What I usually do is copy & paste the client's info into Exce
& then I type 1,2,3,4,5 or ___ next to each product, then I sor
it and then I copy that data into a "pretty" spreadsheet that i
organized for my boss to calculate some information about the client

What is very time consuming is this "coding" part. I wanted to use I
statements to put the numbers into the cells for me. I found out tha
I cannot use more than 7/8 items in a nested IF, since I have over
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 code
according to column 2 in my ranged data table
However when I use the ranged data and put the function into D3 (th
Code column) it gives me "N/A." The ranged data is in the next shee
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 codin
done? It is very time consuming and of course vulnerable to use
error. I wanted to make some sort of formula or macro so I can simpl
push a button and get the coding part done isntantly

Also, my last question is can I somehow link the VLOOKUP to
different workbook? Then I can just update my master list instead o
having to open 100s of different client workbooks to add in a ne
product

I hope this wasn't too confusing, it seems like it should work, I jus
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

 

[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

 

[Guest]

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]

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 i
their name. Even though they have different names is there anyway t
make it so when it sees a percent sign (%) it codes it always th
same. Should I just do a % with a code in my Products range and the
change VLOOKUP to "true"

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

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

 

[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

 

[Guest]

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.