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.

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

allrelative 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:

=VLOOKUP(B13,Data!$1:$65536,16,0)

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

lookup_array?

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.

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_arrayargument.Why not take advantage of the work somebody did for you and read the Help file? It might even help

moreif 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.

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

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History