I’m fairly new to spreadsheets and would appreciate a little help with a problem.
I have a Excel workbook, with a worksheet for each customer. It has columns with the date, the hourly rate and the UK RPI (Retail Prices Index). The hourly rate for each customer changes on the anniversary of the contract start date each year in line the RPI. I have another worksheet that has columns for the date and the RPI percentage increase. This is updated each month when the RPI is announced.
In the first worksheet, I have added the following in the RPI column: =VLOOKUP(A4,RPI!$A$1:$B$64,2). This gets the percentage from the RPI worksheet with the same date as the hourly rate.
This works OK, except that when I contact the customer to give them the good news it is a month before the new rate comes into force. Also, the RPI itself is a month late (the RPI for April was announced yesterday, 18 May).
How can I change the formula above to look for the date two months earlier than the date it is to take effect? So, contacting a customer in May about a renewal in June, I will have to use the RPI for April, because it is the latest information I have.