vlookup problems

[djohns158]

My vlookup of customer information is working fine until I add a
customer to the list. The vlookup formula looks fine and it's
referencing the proper worksheet (customer info). The range is fine
(I'm assuming if the data range encompasses the entire worksheet it's
fine) and the column indexes are correct. Yet, no data is returned to
the destination cells. This only started happening after I added some
customers to the customer info sheet. I'm not sure if this matters,
but I've manually updated the source links also, hoping this would
help. It didn't. Are there any other things I should be looking at
when troubleshooting? Thank you in advance for your help.

Daryl

 

[scott]

could we see your formula?

 

[djohns158]

could we see your formula?






- Show quoted text -

=VLOOKUP(E7,'C:\Documents and Settings\daryl\Desktop\Cust Maint\
[Vendors.xlsx]Vendor List (2)'!$1:$1048576,3,FALSE)

This vlookup is used for 7 other fields and the only difference is the
column index.

 

[Don Guillett]

[Vendors.xlsx]Vendor List (2)'!$1:$1048576,3,FALSE)
[Vendors.xlsx]Vendor List (2)'!$1:$1048576,column(),FALSE)


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

could we see your formula?






- Show quoted text -

=VLOOKUP(E7,'C:\Documents and Settings\daryl\Desktop\Cust Maint\
[Vendors.xlsx]Vendor List (2)'!$1:$1048576,3,FALSE)

This vlookup is used for 7 other fields and the only difference is the
column index.

 

[scott]

Perhaps I am misunderstanding the formula,
but are there columns defined in the range?
such as:
=VLOOKUP(E7,'C:\Documents and
Settings\daryl\Desktop\CustMaint\[Vendors.xlsx]Vendor List
(2)'!$A$1:$G$1048576,3,FALSE)

?

 

[Tyro]

$1:$1048576 defines every row from 1 through 1048576 and thus every column
in each row

Tyro

Perhaps I am misunderstanding the formula,
but are there columns defined in the range?
such as:
=VLOOKUP(E7,'C:\Documents and
Settings\daryl\Desktop\CustMaint\[Vendors.xlsx]Vendor List
(2)'!$A$1:$G$1048576,3,FALSE)

?

=VLOOKUP(E7,'C:\Documents and Settings\daryl\Desktop\Cust Maint\
[Vendors.xlsx]Vendor List (2)'!$1:$1048576,3,FALSE)

 

[Dave Peterson]

Tyro explained that the original range included all the cells in the worksheet,
but I agree with your point.

I'd also limit the number of columns to just what I need.

I'd use something like: $a:$g

Then the formula wouldn't think it would have to recalculate when I changed
something out side the range I needed--like in column X.

Perhaps I am misunderstanding the formula,
but are there columns defined in the range?
such as:
=VLOOKUP(E7,'C:\Documents and
Settings\daryl\Desktop\CustMaint\[Vendors.xlsx]Vendor List
(2)'!$A$1:$G$1048576,3,FALSE)

?

=VLOOKUP(E7,'C:\Documents and Settings\daryl\Desktop\Cust Maint\
[Vendors.xlsx]Vendor List (2)'!$1:$1048576,3,FALSE)

 

[scott]

I was actually ignorant on the columns being defined this way.
Is it possible that with such a huge range, calculation has never been
completed?
Just grasping to understand.
Thanks
scott

Tyro explained that the original range included all the cells in the worksheet,
but I agree with your point.

I'd also limit the number of columns to just what I need.

I'd use something like: $a:$g

Then the formula wouldn't think it would have to recalculate when I changed
something out side the range I needed--like in column X.

Perhaps I am misunderstanding the formula,
but are there columns defined in the range?
such as:
=VLOOKUP(E7,'C:\Documents and
Settings\daryl\Desktop\CustMaint\[Vendors.xlsx]Vendor List
(2)'!$A$1:$G$1048576,3,FALSE)

?

=VLOOKUP(E7,'C:\Documents and Settings\daryl\Desktop\Cust Maint\
[Vendors.xlsx]Vendor List (2)'!$1:$1048576,3,FALSE)

 

[Tyro]

$1:$1048576 involves 1,048,576 rows * 16,384 columns = 17,179,869,184
cells. Do you really need that number of rows and columns in the data for
your lookup array? Could you get by with, say 1,000 rows and 10 columns? Or
do you really over 17 billion cells involved in your lookup array?

Tyro

I was actually ignorant on the columns being defined this way.
Is it possible that with such a huge range, calculation has never been
completed?
Just grasping to understand.
Thanks
scott

Tyro explained that the original range included all the cells in the
worksheet,
but I agree with your point.

I'd also limit the number of columns to just what I need.

I'd use something like: $a:$g

Then the formula wouldn't think it would have to recalculate when I
changed
something out side the range I needed--like in column X.

Perhaps I am misunderstanding the formula,
but are there columns defined in the range?
such as:
=VLOOKUP(E7,'C:\Documents and
Settings\daryl\Desktop\CustMaint\[Vendors.xlsx]Vendor List
(2)'!$A$1:$G$1048576,3,FALSE)

?

On Fri, 22 Feb 2008 10:12:57 -0800 (PST), (e-mail address removed) wrote:

=VLOOKUP(E7,'C:\Documents and Settings\daryl\Desktop\Cust Maint\
[Vendors.xlsx]Vendor List (2)'!$1:$1048576,3,FALSE)

 

[Tyro]

In Excel 2007 A:A defines column A which defines all column A entries in all
rows of column A, that is: 1,048,576 rows * 1 column = 1,048,576 cells. 1:1
defines row 1 which defines all row 1 entries for all of the columns in row
1, that is 1 row * 16,384 columns = 16,384 cells

Tyro

I was actually ignorant on the columns being defined this way.
Is it possible that with such a huge range, calculation has never been
completed?
Just grasping to understand.
Thanks
scott

Tyro explained that the original range included all the cells in the
worksheet,
but I agree with your point.

I'd also limit the number of columns to just what I need.

I'd use something like: $a:$g

Then the formula wouldn't think it would have to recalculate when I
changed
something out side the range I needed--like in column X.

Perhaps I am misunderstanding the formula,
but are there columns defined in the range?
such as:
=VLOOKUP(E7,'C:\Documents and
Settings\daryl\Desktop\CustMaint\[Vendors.xlsx]Vendor List
(2)'!$A$1:$G$1048576,3,FALSE)

?

On Fri, 22 Feb 2008 10:12:57 -0800 (PST), (e-mail address removed) wrote:

=VLOOKUP(E7,'C:\Documents and Settings\daryl\Desktop\Cust Maint\
[Vendors.xlsx]Vendor List (2)'!$1:$1048576,3,FALSE)

 

[Dave Peterson]

Lots of excel's functions limit themselves to the used range. So I wouldn't be
surprised if there wasn't much of a calculation hit in most cases.

But I bet you've seen excel not reset its used range after you type something
way past the "normal" used range and then deleted/cleared the cell.

I think I'd be more worried about how often the function wants to recalculate.
If I limit my lookup range to A:G (say) and make changes in Hlastcolumn), then
it won't recalc.

Any change in that lookup range is going to make the function (all the
functions!) that use that range recalculate.

I guess that could be a reason to use =index(match()) instead of =vlookup().

This only depends on a couple of columns:
=index(sheet2!z:z,match(a1,sheet2!a:a,0))

instead of using 26 columns:
=vlookup(a1,sheet2!a:z,26,false)

I was actually ignorant on the columns being defined this way.
Is it possible that with such a huge range, calculation has never been
completed?
Just grasping to understand.
Thanks
scott

Tyro explained that the original range included all the cells in the worksheet,
but I agree with your point.

I'd also limit the number of columns to just what I need.

I'd use something like: $a:$g

Then the formula wouldn't think it would have to recalculate when I changed
something out side the range I needed--like in column X.

Perhaps I am misunderstanding the formula,
but are there columns defined in the range?
such as:
=VLOOKUP(E7,'C:\Documents and
Settings\daryl\Desktop\CustMaint\[Vendors.xlsx]Vendor List
(2)'!$A$1:$G$1048576,3,FALSE)

?

On Fri, 22 Feb 2008 10:12:57 -0800 (PST), (e-mail address removed) wrote:

=VLOOKUP(E7,'C:\Documents and Settings\daryl\Desktop\Cust Maint\
[Vendors.xlsx]Vendor List (2)'!$1:$1048576,3,FALSE)