| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to Create VLookup for a range of specified grades? | Excel Worksheet Functions | |||
| Vlookup in large named range | Excel Worksheet Functions | |||
| Getting #N/A from Vlookup when matching value exist in the lookup data range. | Excel Worksheet Functions | |||
| Open Workbook - Select Range as table for vlookup | Excel Discussion (Misc queries) | |||
| How do I use Range Names listed in a VLookup table in a formula? | Excel Worksheet Functions | |||
Linear Mode
