formula question

  • Thread starter Thread starter abraun
  • Start date Start date
A

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?
 
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
 
abraun said:
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?
Click to expand...


Edit the original formula to be
=LOOKUP(c3,Lists!$aa$2:$aa$113,Lists!$ab$2:ab$113)
See the extra $ signs?
 
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
 
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
 
Back
Top