How to apply similar formula to all cells in a column?

I have three columns in an excel sheet, the first column has a dollar value, the second is the tax on that dollar value and the third is the vendor to which the dollar amount was paid to. Only certain vendors, however are tax exempt. What I would like to do is enter the dollar value, select my vendor and have the tax calculated. On a separate sheet, I've set up a vendors list along with their tax exemption status, yes or no. This allows me to have a pull down list of vendors using the data validation method to select my vendor. The dollar amount is entered manually and for the tax calculation I use the following formula
=IF(VLOOKUP(C3, Sheet2!A3:B5, 2, FALSE) = "yes", A3*0.05, " ")
The C column is the vendor entry
Sheet2 is the vendor list
The A column is the dollar amount entry

For this particular row, it takes the vendor(C3), locates it in the A column of my vendor list (Sheet2!) and returns a yes or no from the B column. If it's "yes" then it calculates a 5% tax on the dollar amount(A3), if not it leaves it blank.

For the next row, the formula should look like this
=IF(VLOOKUP(C4, Sheet2!A3:B5, 2, FALSE) = "yes", A4*0.05, " ")
only the vendor(C4) and dollar amount(A4) change not the lookup table.

However, if I click on the bottom right corner of the first cell in the tax column and extend it to the next cell down so as to copy the formula it ends up with this
=IF(VLOOKUP(C4, Sheet2!A4:B6, 2, FALSE) = "yes", A4*0.05, " ")
You will notice that the lookup tables parameters have changed from A3:B5 to A4:B6.

How can I apply the same formula to all cells in a column and control which variables are affected?

Hi,

To do what you want you need to lock the refrence cells using $.

sample =IF(VLOOKUP(C3, Sheet2!$A$3:$B$5, 2, FALSE) = "yes", A3*0.05, " ")

When you fill down the ref on sheet2 will not change.

Cheers

Tony