Avatar of gerlis
gerlis

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

Avatar of undefined
Last Comment
gerlis
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Blurred text
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.
See Pricing Options
Start Free Trial
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

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
Avatar of gerlis
gerlis

ASKER

Brilliant.  Worked perfectly.  Thanks.
Microsoft Excel
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
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo