# vlookup limitations?

L

#### Larry

I need help with an Excel spreadsheet. I have a workbook with two sheets.
Second sheet has 5 columns and 800 rows. Columns are:
Customers Street City State ZIP Code

I need Excel to put the adress information relating to the customer (column
1) into 4 cells on the first sheet depending upon which customer is entered
into a cell on the first sheet.

I was able to get this to work except when I get below the 160th row no
information is pulled (N/A). Is there a record limitation?

G

#### Gary''s Student

I have VLOOKUP working on full columns; 65535 rows in pre-2007 Excel.

160 items should not be a problem itself.

M

#### Max

Perhaps you could post your actual formula used?

L

#### Larry

Thanks for the info. I guess that there is something wrong with my syntax!
Got any suggestions?

S

#### Stephen

What is your syntax? Post the formula you use!

Larry said:
Thanks for the info. I guess that there is something wrong with my
syntax!
Got any suggestions?

L

#### Larry

Currently I am only pulling in the Street information (column2). The cell on
sheet one where the customer is entered is I8. Formula is:
vlookup(I8,customers,2,false). Customers is the name of the second sheet. If
I choose a customer on a row below 160 it does not return any street. If I
change the false to true I get the address of the customer on row 160.
Thanks

M

#### Max

In Sheet1,

Think you could try something like this in say, J8:
=VLOOKUP(I8,customers!A:B,2,FALSE)

Here, it's correct to use FALSE to look for an exact match.
Believe the prob was in your table array, this part: customers!A:B

S

#### Stephen

Is "customers" a named range on the worksheet containing your data? Possibly
it is defined only as far as row 160? Look at the definition in
Insert > Name > Define

D

#### Dave Peterson

Maybe you defined Customers incorrectly.

What does it show in the "refers to" box (under the Insert|Name|Define dialog)

L

#### Larry

Thanks to both of you, both of you are right. Thanks again.

M

#### Max

.. vlookup(I8,customers,2,false).
Customers is the name of the second sheet.

thought you might have crafted the table array wrongly. Afterall, you
re-typed the formula in your response (with possible typos, omissions, etc)
instead of just copying n pasting direct from the formula bar

If you had put the 2nd line as:
Customers is the name *on* the second sheet.
then I would have read "Customers" as a defined range

---