Variable equation help....VAT issue.

Hi...having trouble with a calculation regarding the new VAT (value added tax) rules in the UK, and need your help!!!

I have a file (sample attached) that the user inputs the total price of the product into column G. I then need to work out what the corresponding price is excluding VAT, and how much the VAT equals for each product. This is very easy IF the VAT stays the same, but because of our wonderful government, the VAT level has changed quite a few time over the number of years I've been using this spreadsheet, and will probably change many times in the future!!

So I'm trying to find a way of automatically changing the equation for these values (Column H), which at the moment references the various values in d2 to d4 (but may need more options in the future (e.g. more VAT levels), and so is wrong every time the VAT level changes.

At the moment if / when the VAT changes I have to manually change all the unused entries in column H, and this isn't great as I have thousands of entries in this spreadsheet.

I can't suss out how I can automate this, and so hope you chaps can help!!!

Many Thanks for looking!

Thanks for the reply. However, I have well over 10000 varying dates (sample is only a very small snippet), and I don't know how many changes to the VAT level there may be in the future.

I thought about putting in an "administrator" page which I could then change the VAT level (in some sort of table or slider or indeed a single cell), but this 'latest value' will not change any previous values in the calculated column, but ONLY those cells in that column that have not been already filled. I'm not sure if this is what you are suggesting, or is the same / similar, but could you please explain your idea further?

Many Thanks!

OK, I not used a lookup table, so will investigate.....many thanks for your comments...this looks the way to go. If you have any 'pointers' I would be even more grateful.