vlookup limitations?

[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?

 

[Gary''s Student]

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

160 items should not be a problem itself.

 

[Max]

Perhaps you could post your actual formula used?

 

[Larry]

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

 

[Stephen]

What is your syntax? Post the formula you use!

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

 

[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

 

[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

 

[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
and adjust if necessary.

 

[Dave Peterson]

Maybe you defined Customers incorrectly.

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

 

[Larry]

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

 

[Max]

.. vlookup(I8,customers,2,false).

Customers is the name of the second sheet.

Admittedly, I fell for your 2nd line above in your earlier response, and
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

---