vlookup formula not copying

[djohns158]

I manually put the vlookup formula into one row of my worksheet. It's
working fine. When I try to drag the formula down to the other rows,
it copies everything except the range lookup. It just puts #ref where
the range should be. That has never happened to me before. Is there
something I'm doing wrong? I'm using Excel 2007. Any help would be
appreciated. Thanks, Daryl

 

[PCLIVE]

Post your formula.

 

[djohns158]

Post your formula.

--






- Show quoted text -

=VLOOKUP(B6,'Vdr#'!1:1048576,2,FALSE)

 

[djohns158]

Post your formula.

--






- Show quoted text -

=VLOOKUP(B8,'Vdr#'!#REF!,2,FALSE) This is what happens the formula
becomes when I drag it down to lower cells.

 

[Tyro]

Yes, it will return #REF! as =VLOOKUP(B6,'Vdr#'!1:1048576,2,FALSE)
copied down one row becomes =VLOOKUP(B6,'Vdr#'!2:1048577,2,FALSE) 1048577
is a non-existant row
Using 1:1048576 tells Excel you are using every row in the spreadsheet and
every column, thus 1048576 rows * 16,384 columns = 17,179,869,184 cells
Just how big is your lookup table?

Tyro

Post your formula.

--






- Show quoted text -

=VLOOKUP(B8,'Vdr#'!#REF!,2,FALSE) This is what happens the formula
becomes when I drag it down to lower cells.

 

[djohns158]

Yes, it will return #REF! as =VLOOKUP(B6,'Vdr#'!1:1048576,2,FALSE)
copied down one row becomes =VLOOKUP(B6,'Vdr#'!2:1048577,2,FALSE)  1048577
is a non-existant row
Using 1:1048576 tells Excel you are using every row in the spreadsheet and
every column, thus 1048576 rows * 16,384 columns = 17,179,869,184 cells
Just how big is your lookup table?

Tyro





=VLOOKUP(B8,'Vdr#'!#REF!,2,FALSE)  This is what happens the formula
becomes when I drag it down to lower cells.- Hide quoted text -

- Show quoted text -

My lookup table is 1000 rows and expected to grow.

 

[Tyro]

1000 rows using all 16384 columns in each row?

Tyro

Yes, it will return #REF! as =VLOOKUP(B6,'Vdr#'!1:1048576,2,FALSE)
copied down one row becomes =VLOOKUP(B6,'Vdr#'!2:1048577,2,FALSE) 1048577
is a non-existant row
Using 1:1048576 tells Excel you are using every row in the spreadsheet and
every column, thus 1048576 rows * 16,384 columns = 17,179,869,184 cells
Just how big is your lookup table?

Tyro





=VLOOKUP(B8,'Vdr#'!#REF!,2,FALSE) This is what happens the formula
becomes when I drag it down to lower cells.- Hide quoted text -

- Show quoted text -

My lookup table is 1000 rows and expected to grow.

 

[djohns158]

1000 rows using all 16384 columns in each row?

Tyro







My lookup table is 1000 rows and expected to grow.- Hide quoted text -

- Show quoted text -

No... it's 1000 rows using only the first 12 columns.

 

[Veronica Johnson]

No... it's 1000 rows using only the first 12 columns.- Hide quoted text -

- Show quoted text -

Where's Biff when we need him?!?!? BIFFFF!!!!!!!!!!!!!!!
HELP!!!!!!!!!!!!!!!!!!!!!!!!

 

[Tyro]

When you specify a row like 1:1 you're specifying one row with 16,384
columns
If you use 1000 rows with 12 columns in each row then you should specify it
like A1:L1000
You could even start out with 2000 rows with A1:L2000 and grow it as needed
But to use this in a VLOOKUP and drag the formula down, you will need to use
absolute addresses for the lookup table as in

=VLOOKUP(B6,'Vdr#'!$A$1:$L$2000,2,FALSE)

The B6 will change to B7 then to B8 etc, but the lookup table address won't
change.

Tyro

1000 rows using all 16384 columns in each row?

Tyro







My lookup table is 1000 rows and expected to grow.- Hide quoted text -

- Show quoted text -

No... it's 1000 rows using only the first 12 columns.

 

[PCLIVE]

For performance reasons, I would limit the rows if possible. However,
couldn't you just make the rows constant?

=VLOOKUP(B6,'Vdr#'!$1:$1048576,2,FALSE)

Is there any reason why you wouldn't want to specify the columns instead of
using entire rows?

=VLOOKUP(B7,'Vdr#'!$A:$B,2,FALSE)


HTH,
Paul



--

1000 rows using all 16384 columns in each row?

Tyro







My lookup table is 1000 rows and expected to grow.- Hide quoted text -

- Show quoted text -

No... it's 1000 rows using only the first 12 columns.

 

[Dave Peterson]

I like your second formula.

I bet that excel will know to use just the usedrange in its =vlookup().

And by limiting the range to just A:B, you don't have to worry about the formula
recalculating when something changes in column C to whatever the last column is
in xl2007.

 

[djohns158]

When you specify a row like 1:1 you're specifying one row with 16,384
columns
If you use 1000 rows with 12 columns in each row then you should specify it
like A1:L1000
You could even start out with 2000 rows with A1:L2000 and grow it as needed
But to use this in a VLOOKUP and drag the formula down, you will need to use
absolute addresses for the lookup table as in

=VLOOKUP(B6,'Vdr#'!$A$1:$L$2000,2,FALSE)

The B6 will change to B7 then to B8 etc, but the lookup table address won't
change.

Tyro







No... it's 1000 rows using only the first 12 columns.- Hide quoted text -

- Show quoted text -

I tried that formula. It works, except the lookup range keeps
changing as I go down the rows. I'll paste the formula as it appears
in 3 of the cells on different rows: =VLOOKUP(B2,'Vdr#'!
A1:K2000,2,FALSE) this is on row 2. =VLOOKUP(B4,'Vdr#'!
A3:K2002,2,FALSE) this is on row 4. =VLOOKUP(B9,'Vdr#'!
A8:K2007,2,FALSE) this is on row 8. Notice the range changing as I
go down. I need the range to stay exactly the same throughout the
whole table. Thanks in advance. Daryl

 

[PCLIVE]

Thanks Dave,

That does seem to make more sense.

--

 

[Tyro]

The formula I showed you was =VLOOKUP(B6,'Vdr#'!$A$1:$L$2000,2,FALSE)

See the dollar signs? You didn't put them in your formula
=VLOOKUP(B2,'Vdr#'!A1:K2000,2,FALSE)
You used relative addressing so the addresses change. The dollar signs make
the addresses absolute so they don't change

Tyro

When you specify a row like 1:1 you're specifying one row with 16,384
columns
If you use 1000 rows with 12 columns in each row then you should specify
it
like A1:L1000
You could even start out with 2000 rows with A1:L2000 and grow it as
needed
But to use this in a VLOOKUP and drag the formula down, you will need to
use
absolute addresses for the lookup table as in

=VLOOKUP(B6,'Vdr#'!$A$1:$L$2000,2,FALSE)

The B6 will change to B7 then to B8 etc, but the lookup table address
won't
change.

Tyro







No... it's 1000 rows using only the first 12 columns.- Hide quoted text -

- Show quoted text -

I tried that formula. It works, except the lookup range keeps
changing as I go down the rows. I'll paste the formula as it appears
in 3 of the cells on different rows: =VLOOKUP(B2,'Vdr#'!
A1:K2000,2,FALSE) this is on row 2. =VLOOKUP(B4,'Vdr#'!
A3:K2002,2,FALSE) this is on row 4. =VLOOKUP(B9,'Vdr#'!
A8:K2007,2,FALSE) this is on row 8. Notice the range changing as I
go down. I need the range to stay exactly the same throughout the
whole table. Thanks in advance. Daryl

 

[PCLIVE]

Why are you specifying a lookup table 12 columns wide? VLOOKUP will lookup
a value in the left-most column of your table-array and then return a value
from the column number of that array that you specify. Since you are
specifying the second column of the table-array to return the result from,
then your range does not need to be more than two columns wide.

=VLOOKUP(B7,'Vdr#'!$A:$B,2,FALSE)

HTH,
Paul

--

When you specify a row like 1:1 you're specifying one row with 16,384
columns
If you use 1000 rows with 12 columns in each row then you should specify
it
like A1:L1000
You could even start out with 2000 rows with A1:L2000 and grow it as
needed
But to use this in a VLOOKUP and drag the formula down, you will need to
use
absolute addresses for the lookup table as in

=VLOOKUP(B6,'Vdr#'!$A$1:$L$2000,2,FALSE)

The B6 will change to B7 then to B8 etc, but the lookup table address
won't
change.

Tyro







No... it's 1000 rows using only the first 12 columns.- Hide quoted text -

- Show quoted text -

I tried that formula. It works, except the lookup range keeps
changing as I go down the rows. I'll paste the formula as it appears
in 3 of the cells on different rows: =VLOOKUP(B2,'Vdr#'!
A1:K2000,2,FALSE) this is on row 2. =VLOOKUP(B4,'Vdr#'!
A3:K2002,2,FALSE) this is on row 4. =VLOOKUP(B9,'Vdr#'!
A8:K2007,2,FALSE) this is on row 8. Notice the range changing as I
go down. I need the range to stay exactly the same throughout the
whole table. Thanks in advance. Daryl

 

[djohns158]

The formula I showed you was  =VLOOKUP(B6,'Vdr#'!$A$1:$L$2000,2,FALSE)

See the dollar signs?  You didn't put them in your formula
=VLOOKUP(B2,'Vdr#'!A1:K2000,2,FALSE)
You used relative addressing so the addresses change. The dollar signs make
the addresses absolute so they don't change

Tyro







I tried that formula.  It works, except the lookup range keeps
changing as I go down the rows.  I'll paste the formula as it appears
in 3 of the cells on different rows:  =VLOOKUP(B2,'Vdr#'!
A1:K2000,2,FALSE)  this is on row 2.   =VLOOKUP(B4,'Vdr#'!
A3:K2002,2,FALSE)  this is on row 4.  =VLOOKUP(B9,'Vdr#'!
A8:K2007,2,FALSE)  this is on row 8.  Notice the range changing as I
go down.  I need the range to stay exactly the same throughout the
whole table.  Thanks in advance.  Daryl- Hide quoted text -

- Show quoted text -

Thank you so much.... thanks for your patience. Worked perfectly!!!
Thank you again!!!