troubleshooting Question

How to make VLOOKUP find a the correct row in another worksheet

Avatar of gerlis
gerlis asked on
Office ProductivitySpreadsheetsMicrosoft Excel
3 Comments1 Solution583 ViewsLast Modified:
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.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
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.

-Mike Kapnisakis, Warner Bros