Replicating Vlookup Formula

fbcentral · 07-11-2008, 06:25 AM

Hi
I am currently creating a work book with several sheets (excel 2007), on 1 sheet i have a list of data on the second I am trying to pull a whole row of data by by entering a value in the first colum this works all ok using vlookup formula however i need to replicate the same formula down the page but when i do the table array part of the formula rolls on 1 each time it replicates

=IF(C3="","",VLOOKUP(C3,Ingridients!A2:I203,9,FALSE))

=IF(C3="","",VLOOKUP(C3,Ingridients! A3:I204,9,FALSE))

If I only had a few to do i woulod change it manuall but i have about 400 rows to do each with 6 seperate cells.

To complicate it further cell C3 does need to roll on i.e C3 - C4 -C5 ect on each replication.

If anyone could help I would really appreciate it.

N

David M58 · 07-11-2008, 08:34 AM

You can use the $ character to create absolute and mixed cell references in your formulas. If you enter your formula like this:

=IF($C3="","",VLOOKUP($C3,Ingridients!$A$2:$I$203,9,FALSE))

When you copy that cell to the cell directly below, the result is:

=IF($C4="","",VLOOKUP($C4,Ingridients!$A$2:$I$203,9,FALSE))

07-11-2008, 06:25 AM	#1 (permalink)
fbcentral Registered User Join Date: Jul 2008 Posts: 1 OS: Vista	Replicating Vlookup Formula Hi I am currently creating a work book with several sheets (excel 2007), on 1 sheet i have a list of data on the second I am trying to pull a whole row of data by by entering a value in the first colum this works all ok using vlookup formula however i need to replicate the same formula down the page but when i do the table array part of the formula rolls on 1 each time it replicates =IF(C3="","",VLOOKUP(C3,Ingridients!A2:I203,9,FALSE)) =IF(C3="","",VLOOKUP(C3,Ingridients! A3:I204,9,FALSE)) If I only had a few to do i woulod change it manuall but i have about 400 rows to do each with 6 seperate cells. To complicate it further cell C3 does need to roll on i.e C3 - C4 -C5 ect on each replication. If anyone could help I would really appreciate it. N

07-11-2008, 08:34 AM	#2 (permalink)
David M58 Registered User Join Date: May 2008 Location: Baltimore, Maryland Posts: 160 OS: Windows XP SP3	Re: Replicating Vlookup Formula You can use the $ character to create absolute and mixed cell references in your formulas. If you enter your formula like this: =IF($C3="","",VLOOKUP($C3,Ingridients!$A$2:$I$203,9,FALSE)) When you copy that cell to the cell directly below, the result is: =IF($C4="","",VLOOKUP($C4,Ingridients!$A$2:$I$203,9,FALSE)) Last edited by David M58; 07-11-2008 at 08:37 AM.