asked on

# 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,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.
Office ProductivitySpreadsheetsMicrosoft Excel

Last Comment
gerlis
ASKER CERTIFIED SOLUTION
Patrick Matthews

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Rob Henson

Assuming the date looked up needs to be the beginning of the month ie for today it would  be 01/04/2011 use

=VLOOKUP(EOMONTH(A4,-2)+1,RPI!\$A\$1:\$B\$64,2)

EOMONTH needs the analysis toolpak addin enabled.

Cheers
Rob H
gerlis

ASKER

Brilliant.  Worked perfectly.  Thanks.
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts

TRUSTED BY