Results 1 to 3 of 3

Thread: How to Copy formula using Vlookup on Excel?

  1. #1
    Join Date
    Nov 2011
    Posts
    60

    How to Copy formula using Vlookup on Excel?

    Dear all,
    I have a sheet with some formulas, i need the same formulas in the new
    sheet.

    Eg: -
    A1 A2
    A3 A4 A5 A6
    Code Customer Name Oct 11 Sep 11 Aug 11 Total
    CAA001 ABUDHABI HOSPITALITY CO. 11,456 8,422 28,063 =sum(A3:A5)

    can i able to copy only formula (A6) with reference to the colomn A1 using Vlookup or any other formula to the new sheet. Any help will be highly appreciated. Thanks a lot in advance.

  2. #2
    Join Date
    Dec 2010
    Posts
    351

    Re: How to Copy formula using Vlookup on Excel?

    VLOOKUP() returns a value. What you need is a combination of MATCH() and OFFSET() to get the address of the cell. Then use a tiny bit of VBA to perform the copy/paste.

  3. #3
    Join Date
    Mar 2011
    Posts
    542

    Re: How to Copy formula using Vlookup on Excel?

    Your question is unclear. We do not use VLOOKUP to copy formulas; instead, we use it to look up values.

    If you truly wanted to copy formulas from one sheet to another, you would use either copy-and-paste or the Copy Sheet feature (right-click on the sheet tab).

    But I suspect you want to look up codes in the 1st column (A) and return the corresponding sum from the 6th column (F). Thus, if your table of data is in Sheet2, you might write:

    Code:
    =VLOOKUP(C2,Sheet2!$A:$F,6,FALSE)
    to look up a code in column C in Sheet1 and return the corresponding sum.

    It would be better if the table in Sheet2 were sorted in ascending order according to the codes in column 1. In that case, you might write:

    Code:
    =VLOOKUP(C2,Sheet2!$A$2:$F$1000,6)
    assuming that the Sheet2 table is in rows 2 through 1000.

    Note.... In your example, you labeled the columns of data A1, A2, A3, etc. Since you presented them as columns of data, I assume you meant to label them as A2, B2, C2 etc, allowing for headings in row 1. But if your table is truly in rows 1 through 6 of columns A, B, C etc, you would use HLOOKUP instead of VLOOKUP. For example:

    Code:
    =HLOOKUP(C2,Sheet2!$1:$6,6,FALSE)
    or

    Code:
    =HLOOKUP(C2,Sheet2!$A$1:$Z$6,6)
    Again, the second form assumes that the data is sorted in ascending order according to the codes in row 1.

Similar Threads

  1. Need help to get rid of #n/a error in Excel Vlookup
    By Culpritism in forum MS Office Support
    Replies: 2
    Last Post: 24-02-2012, 07:39 PM
  2. Convert copy formula to Text in Microsoft Excel
    By Kaesav in forum MS Office Support
    Replies: 2
    Last Post: 01-02-2012, 07:39 PM
  3. How to get yes or no in Vlookup in Excel
    By Gurmeet in forum MS Office Support
    Replies: 2
    Last Post: 24-01-2012, 04:57 PM
  4. Excel - Multiple vlookup
    By mike_2011 in forum Windows Software
    Replies: 1
    Last Post: 28-07-2011, 03:40 AM
  5. Copy paste formula automatically in Excel 2007
    By Macadrian in forum Software Development
    Replies: 3
    Last Post: 12-11-2009, 08:19 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,689,780.37161 seconds with 17 queries