formula question

[abraun]

I am using the following fomula
=LOOKUP(c3,Lists!$aa2:$aa113,Lists!$ab2:ab113)
the formulas works great, however in trying to copy the formula, it adds a
number to the list values and looks like this.
=LOOKUP(c3,Lists!$aa3:$aa114,Lists!$ab3:ab114)
the list I am wanting to work with starts at aa1 and ends at aa113, so this
will not work.
I do not want to edit the formula in each cell, as I am wanting to fill
acouple thousand cells.
any suggestions?

 

[jaf]

Hi,
You need to add more dollars.
Change =LOOKUP(c3,Lists!$aa2:$aa113,Lists!$ab2:ab113)
to =LOOKUP(c3,Lists!$aa$2:$aa$113,Lists!$ab$2:ab$113)

$a locks the column $2 locks the row.
John

 

[Gordon]

I am using the following fomula
=LOOKUP(c3,Lists!$aa2:$aa113,Lists!$ab2:ab113)
the formulas works great, however in trying to copy the formula, it adds a
number to the list values and looks like this.
=LOOKUP(c3,Lists!$aa3:$aa114,Lists!$ab3:ab114)
the list I am wanting to work with starts at aa1 and ends at aa113, so
this
will not work.
I do not want to edit the formula in each cell, as I am wanting to fill
acouple thousand cells.
any suggestions?

Edit the original formula to be
=LOOKUP(c3,Lists!$aa$2:$aa$113,Lists!$ab$2:ab$113)
See the extra $ signs?

 

[francis]

Hi
Click on the cell with the correct formula
Go to the formula bar
place your cursor in the $aa2:$aa113 and $ab2:ab113
press F4 key till you see
=LOOKUP(c3,Lists!$aa$2:$aa$113,Lists!$ab$2:$ab$113)
Excel will add the $ for you, then copy down

--
Hope this help

Pls click the Yes button below if this post provide the answer you asked


cheers, francis

 

[mikebres]

You would probably also find it usefull to search the Help for "About cell
and range references" and read up on the "The difference between relative and
absolute references"

Mike