Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Question - Can I Keep the Same Aray with Drag?
I am using VLOOKUP to match a unique number from my Table A to a unique
number from Table B and return whatever it says in the 3rd column of Table B. The first one worked great. But I need to copy that formula through an entire column, and as I FILL it to other cells, it changes the array and suddenly I get N/A# a lot. I have to go back and manually cut and paste the array value into each formula and then they work. This is highly annoying. is there anyway to tell Excel "Do not change this value - always keep this array the same"? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Question - Can I Keep the Same Aray with Drag?
Use absolute references for the table_array:
=VLOOKUP(A1,$H$1:$K$100,3,0) -- Biff Microsoft Excel MVP "CJ" wrote in message ... I am using VLOOKUP to match a unique number from my Table A to a unique number from Table B and return whatever it says in the 3rd column of Table B. The first one worked great. But I need to copy that formula through an entire column, and as I FILL it to other cells, it changes the array and suddenly I get N/A# a lot. I have to go back and manually cut and paste the array value into each formula and then they work. This is highly annoying. is there anyway to tell Excel "Do not change this value - always keep this array the same"? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Question - Can I Keep the Same Aray with Drag?
Hey CJ,
The problem is that you haven't anchored your data set. Use $ in the vlookup. ex. =VLOOKUP(A1,$C$7:$H$38,3,FALSE) -- -SA "CJ" wrote: I am using VLOOKUP to match a unique number from my Table A to a unique number from Table B and return whatever it says in the 3rd column of Table B. The first one worked great. But I need to copy that formula through an entire column, and as I FILL it to other cells, it changes the array and suddenly I get N/A# a lot. I have to go back and manually cut and paste the array value into each formula and then they work. This is highly annoying. is there anyway to tell Excel "Do not change this value - always keep this array the same"? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Question - Can I Keep the Same Aray with Drag?
Also, whenever you specific a cell or a data range, the F4 key will scroll
you through the combinations of absolute and relative references. -- Tips for Excel, Word, PowerPoint and Other Applications http://www.kan.org/tips "CJ" wrote: I am using VLOOKUP to match a unique number from my Table A to a unique number from Table B and return whatever it says in the 3rd column of Table B. The first one worked great. But I need to copy that formula through an entire column, and as I FILL it to other cells, it changes the array and suddenly I get N/A# a lot. I have to go back and manually cut and paste the array value into each formula and then they work. This is highly annoying. is there anyway to tell Excel "Do not change this value - always keep this array the same"? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP Question - Can I Keep the Same Aray with Drag?
You guys rock! Worked perfectly! Thanks so much!!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drag & drop in vlookup function | Excel Discussion (Misc queries) | |||
How to write Vlookup to drag across a row? | Excel Discussion (Misc queries) | |||
Vlookup question | Excel Worksheet Functions | |||
VLOOKUP question...it probably can't... | Excel Discussion (Misc queries) | |||
VLOOKUP question | Excel Worksheet Functions |