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

Advertisements

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.
 
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
 
Ad

Advertisements

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

Dave Peterson

Maybe you defined Customers incorrectly.

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

Advertisements

M

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

---
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top