VLOOKUP help. Im an Excel Newbie!!1

[iDontLikeExcel]

I have an Invoice worksheet which I must use VLOOKUP function to import
data such as customer name, customer code, customer address, etc.

My study sheets didn't explain how to use VLOOKUP for obtaining data
from different files.


Basically on the Invoice worksheet, in the cell under customer code use
the VLOOKUP function to enter the customers name (from the table on the
customers worksheet). In the cell under customers name use VLOOKUP to
enter customers address, in the cell under customers address use
VLOOKUP to enter suburb,state and postcode (from customer worksheet).

Then I need to use VLOOKUP to enter the appropriate product description
and unit price from my products worksheet.

There is more but for now if someone could help me out I will be very
happy.

I will attach the worksheets.

File Attached: http://www.excelforum.com/attachment.php?postid=332741 (invoice.xls)

 

[iDontLikeExcel]

Customer worksheet...

File Attached: http://www.excelforum.com/attachment.php?postid=332742 (customer.xls)

 

[iDontLikeExcel]

Product worksheet...

File Attached: http://www.excelforum.com/attachment.php?postid=332744 (product.xls)

 

[wiseman]

This web site has instructions.
http://www.uic.edu/depts/accc/seminars/excel2000-intermed/lookup.html
also have you tried the help in excel, press F1, go to index and type in
vlookup.
Barbara

 

[Dave Peterson]

If your table is set up with the key value (common value between the list and
the table) in the leftmost column, then you can use =vlookup().

Say your table is on sheet2 from A1:E9999 (9999 rows by 5 columns).
And your key value in your list is in sheet1 in cell B12.
And you want to bring back the 4th column (D) in that table for the row that had
an exact match in column A.

Your basic =vlookup() will look like this:

=vlookup(b12,sheet2!$a$1:$e$9999,4,false)

The first parm is the value to match. B12 of the current sheet with what's in
the first column of that table (sheet2!$a$:$a$9999).

Entering the $ signs is nice if you're going to drag the formula down for more
rows.

The 4 means to bring back column 4 from the table (4th column in A:E is D).

The False at the end means that I want an exact match. If you leave that parm
off (or put True), then there doesn't have to be an exact match--but your data
should be sorted to give reasonable results.

(Using True is nice when determining postage stamp type functions. (0-5 ounces
gives something, 5+ to 10 give something else, etc. You wouldn't want to have
to put all the numbers between 0-5 in a table!)

The next thing is that if there isn't a match, you'll get an error back: #n/a.

You can hide them by wrapping your formula inside an error check:

=if(iserror(vlookup(b12,sheet2!$a$1:$e$9999,4,false)),"Missing",
vlookup(b12,sheet2!$a$1:$e$9999,4,false))
(all one cell.)

This will return Missing when there's no match. You could use anything you want
there, including 0 and "".

And if the cell to be returned is empty, excel will return a 0. If you want to
see a cell that looks blank, you can amend your formula like:

=if(iserror(vlookup(b12,sheet2!$a$1:$e$9999,4,false)),"missing",
if(vlookup(b12,sheet2!$a$1:$e$9999,4,false)="","",
vlookup(b12,sheet2!$a$1:$e$9999,4,false)))
(all one cell)

There are some common problems that you could experience:

The key values look the same, but are different.
a. Leading/trailing (and extra internal) spaces make a difference.
b. Numbers that are really text in one list and really numbers in the
other. ('123 (leading single quote) is different from 123 (real number).

========

You posted through exceltip, but the attachments don't propogate to the ms
newsservers (which is a good thing). Many people won't open attachments--fear
of malicious code/viruses. If you have more questions, you might do better by
posting you problem's description in plain text.

 

[ryanb.]

one way:

- in cell C11 (Customer Name): =
IF(ISNA(MATCH($C$10,[cust.xls]Sheet1!$A$5:$A$9,0)),"",VLOOKUP($C$10,[cust.xl
s]Sheet1!$A$5:$F$9,2,FALSE))

- in cell C12 (Customer Address1): =
IF(ISNA(MATCH($C$10,[cust.xls]Sheet1!$A$5:$A$9,0)),"",VLOOKUP($C$10,[cust.xl
s]Sheet1!$A$5:$F$9,3,FALSE))

- in cell C13 (Customer Address 2): =
IF(ISNA(MATCH($C$10,[cust.xls]Sheet1!$A$5:$A$9,0)),"",VLOOKUP($C$10,[cust.xl
s]Sheet1!$A$5:$F$9,4,FALSE)&",
"&VLOOKUP($C$10,[cust.xls]Sheet1!$A$5:$F$9,5,FALSE)&",
"&VLOOKUP($C$10,[cust.xls]Sheet1!$A$5:$F$9,6,FALSE))

- in cell C18 thru C21 (Product Description)(paste in C18 and drag down
using the fill handle): =
IF(ISNA(MATCH($B18,[prod.xls]Sheet1!$A$6:$A$14,0)),"",VLOOKUP($B18,[prod.xls
]Sheet1!$A$6:$H$14,2,FALSE))

- in cells E18 thru E21 (Unit Price)(paste in E18 and drag down using fill
handle): =
IF(ISNA(MATCH($B18,[prod.xls]Sheet1!$A$6:$A$14,0)),"",VLOOKUP($B18,[prod.xls
]Sheet1!$A$6:$H$14,6,FALSE))

HTH

ryanb.

 

[iDontLikeExcel]

thx for the help guys. I'm baffled by the language you speak but I will
try my best to work with what you have offered. Thx.

 

[Guest]

You must first defind a range in the source worksheet where the first column in the range is the lookup value
Once this is done you formula (based on what you are doing) willl look as follows

=vlookup(A1,products.xls!range_name,3,false

***"A1" represents that cell that contains the value you are looking u
***"products.lxs" represents the name of the source file the contains the lookup valu
***"range_name" represents the defind range you created in the source fil
***"3" represent the 3rd column from the left of the columun you are performing the lookup in. This column will contain the values that you want returned. (it can be 1,2,3 etc.) It depends are were you are storing the information you want
***False represents an exact match so that if the lookup value is not displayed a #NA will appaer. A value of True will return the closest match

HOPE THIS MAKES IT A LITTLE EASIER