Formula Problem

[Kat]

I have an excel sheet that has almost 4000 data rows. I need to compare the
old sheet to the new sheet and if the part number is equal, I need it to
show me the discount from the old sheet in a column in the new sheet.

Here is the formula I came up with: =LOOKUP(A4,old!A4:A4000,old!H4:H4000)
This compares the A column in the new sheet with the A column in the old
sheet and then will report the discount from the H column into the column
the formula is written. If I hand type the formula in ever cell changing
the row number for the look up cell it works fine. However, when I try to
drag the formula to all 3996 cells, all numbers in the formula add one and
then the formula doesn't work.

How can I enter this formula so that just the beginning cell number changes
but not the rest? Or is there a better formula?

What might I be doing wrong?


Kathy in WI

 

[Dave Peterson]

Maybe you want:

=LOOKUP(A4,old!$A$4:$A$4000,old!$H$4:$H$4000)

 

[Kat]

Thank you that did the trick. My next problem... =)

I now have the discount amount on the new sheet, however some of the new
prices don't actually reflect a price it has a code in the cell which means
call the factory for price. So when I multiply the new price with the
discount, some field reflect an error that reads "#VALUE!" is there anyway
to include in the formula "=G6*H6" that if a dollar amount is not reached
the the cell remains blank instead of the #VALUE! error?

Thanks!

Kathy

 

[Guest]

You could try

=IF(ISERROR(G6*H6),"",G6*H6)


--
Glenton
www.leviqqio.com
Quality financial modelling

Thank you that did the trick. My next problem... =)

I now have the discount amount on the new sheet, however some of the new
prices don't actually reflect a price it has a code in the cell which means
call the factory for price. So when I multiply the new price with the
discount, some field reflect an error that reads "#VALUE!" is there anyway
to include in the formula "=G6*H6" that if a dollar amount is not reached
the the cell remains blank instead of the #VALUE! error?

Thanks!

Kathy