This is the difference between absolute referencing and relative referencing. You want relative referencing for the lookup value but you want the lookup table to be absolutely referenced. To accomplish absolute referencing use "$" for example =IF(VLOOKUP(C4, Sheet2!$A$3:$B$5, 2, FALSE) = "yes", A4*0.05, " "). You can do partial referencing where if you intend to copy a formula across columns but you don't want the reference column to change, use $A1, copying down will still change the row here. The $ stops the character after it from changing when copying cells.