troubleshooting Question
How to make VLOOKUP find a the correct row in another worksheet
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
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.
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
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.
Learn from the best
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.