Copy Vlookup formula from one cell to another

September 26, 2010 at 15:53:57
Specs: Windows XP
I have a VLOOKUP formula set-up that works well however I have 50 cells to fill and when I try to pull the formula down from one cell to another the result is #REF!. So I have to cut and paste special for each cell. The formula is =VLOOKUP(B13,Data!1:65536,16,0).

How can I copy the formula by using the pull-down to automatically fill the other cells? Your help would be appreciated. Thanks.

See More: Copy Vlookup formula from one cell to another

Report •

September 26, 2010 at 16:28:41
I'm not quite sure why you are using the entire sheet as your lookup_array, but here's why you're getting a #REF error:

When you auto-fill down a formula it increments all relative references, including 1:65536.

I'm going to assume that you are using Excel 2003 or earlier since there are only 65536 rows available. When Excel tries to increment that reference to 2:65537, it returns a #REF error.

If you try that same operation in 2007 or beyond, it will work.

To fix it 2003 use Absolute references:


But again, why are you using the entire sheet as the lookup_array?

Report •

September 26, 2010 at 17:00:21
Thank you...that also worked!!! As for using the entire lookup_array...I use it because when I tried using the column alone (16) it returns "N/A" result so I started using the entire lookup_array. Please send your solution for a more efficient way. Thanks again for your help and quick responses.

Report •

September 26, 2010 at 17:28:06
Have you read the Excel Help file on VLOOKUP?

Somebody spend a lot of time writing a pretty good explanation of how to use VLOOKUP and included an example which shows the proper use of the table_array argument.

Why not take advantage of the work somebody did for you and read the Help file? It might even help more if you copy the example to a worksheet (as suggested in the Help file) to help you understand it better.

After you've done that, if you still have questions, come on back and we'll see if we can solve the mystery for you.

Report •

Related Solutions

March 31, 2011 at 00:55:27
i have one data with names in 1st sheet horizontelly and 2nd sheet names with Salary amt vertically what is the formula to get salary amt in 2nd sheet to names in 1st sheet

Report •

Ask Question