Nokia C3: How to Copy formula using Vlookup on Excel?

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.
Reply With Quote
#2
Old 04-01-2012
Abhiroopa Abhiroopa is offline
Member

Join Date: Dec 2010
Posts: 342
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.
Reply With Quote
#3
Old 04-01-2012
Erakna Erakna is offline
Member

Join Date: Mar 2011
Posts: 507
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.
Reply With Quote