vlookup range changes
 

I need help with vLookup. Excel changes my array range.

I have my price list with 100 product numbers and matching prices. I have
another list of products, that I'm trying fill in the prices for.

Col A - product numbers
Col B - prices
Col C - a list of product numbers I'm trying to get prices for
Col D - where I want to put the correct price for the product in column C

In D1, I put in the formula
vlookup(C1,A1:b100,2, true)
and then I copy the formula to the rest of column D

The problem is, when Excel copies the formula, it changes the lookup range.
Example, in D45, the formula actually looks like
vlookup(c45,a45:b100,2,true)

Somehow it changed the lookup range to start at a45.

Should I be using a different function?

vlookup range changes
 

You need to lock-in the lookup table reference of your formula.

Instead of D1: =VLOOKUP(C1,A1:B100,2, true)

Use dollar signs ($) in the reference....
D1: =VLOOKUP(C1,$A$1:$B$100,2, true)

When copied and pasted the $A$1:$B$100 in the formula will not change.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Hobbes2006" wrote:

I need help with vLookup. Excel changes my array range.

I have my price list with 100 product numbers and matching prices. I have
another list of products, that I'm trying fill in the prices for.

Col A - product numbers
Col B - prices
Col C - a list of product numbers I'm trying to get prices for
Col D - where I want to put the correct price for the product in column C

In D1, I put in the formula
vlookup(C1,A1:b100,2, true)
and then I copy the formula to the rest of column D

The problem is, when Excel copies the formula, it changes the lookup range.
Example, in D45, the formula actually looks like
vlookup(c45,a45:b100,2,true)

Somehow it changed the lookup range to start at a45.

Should I be using a different function?

vlookup range changes
 

Yes, that solved the problem.

Thank you.

"Ron Coderre" wrote:

You need to lock-in the lookup table reference of your formula.

Instead of D1: =VLOOKUP(C1,A1:B100,2, true)

Use dollar signs ($) in the reference....
D1: =VLOOKUP(C1,$A$1:$B$100,2, true)

When copied and pasted the $A$1:$B$100 in the formula will not change.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Hobbes2006" wrote:

I need help with vLookup. Excel changes my array range.

I have my price list with 100 product numbers and matching prices. I have
another list of products, that I'm trying fill in the prices for.

Col A - product numbers
Col B - prices
Col C - a list of product numbers I'm trying to get prices for
Col D - where I want to put the correct price for the product in column C

In D1, I put in the formula
vlookup(C1,A1:b100,2, true)
and then I copy the formula to the rest of column D

The problem is, when Excel copies the formula, it changes the lookup range.
Example, in D45, the formula actually looks like
vlookup(c45,a45:b100,2,true)

Somehow it changed the lookup range to start at a45.

Should I be using a different function?

vlookup range changes
 

Ron's solution works great until you add some new items to your lookup
range.

Try this : =VLOOKUP(C1,A:B,2, true)
This takes care of your problem and another one that you will soon discover.

--
Carlos

"Hobbes2006" wrote in message
...
Yes, that solved the problem.

Thank you.

"Ron Coderre" wrote:

You need to lock-in the lookup table reference of your formula.

Instead of D1: =VLOOKUP(C1,A1:B100,2, true)

Use dollar signs ($) in the reference....
D1: =VLOOKUP(C1,$A$1:$B$100,2, true)

When copied and pasted the $A$1:$B$100 in the formula will not change.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Hobbes2006" wrote:

I need help with vLookup. Excel changes my array range.

I have my price list with 100 product numbers and matching prices. I
have
another list of products, that I'm trying fill in the prices for.

Col A - product numbers
Col B - prices
Col C - a list of product numbers I'm trying to get prices for
Col D - where I want to put the correct price for the product in
column C

In D1, I put in the formula
vlookup(C1,A1:b100,2, true)
and then I copy the formula to the rest of column D

The problem is, when Excel copies the formula, it changes the lookup
range.
Example, in D45, the formula actually looks like
vlookup(c45,a45:b100,2,true)

Somehow it changed the lookup range to start at a45.

Should I be using a different function?