• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

Lookup Value based on Date Span

I have a spreadsheet where I track expenses.

Over time mileage rates, etc. change

I could manually enter an updated rate after each change, but I'd like to do it with a lookup process

Attached is a copy of the spreadsheet - The value I want as the lookup result is on the Expense Tracking Tab - "Rate" and the table I want to use as the source is the mileage lookup tab

Does anybody know of an existing formula / calc that does type of lookup?

Thanks
EPM-Time-Tracking---Expense-Look.xlsx
0
rogerdjr
Asked:
rogerdjr
1 Solution
 
Rgonzo1971Commented:
Hi,
pls try in Cell F2 and under

=IF(D2="Mileage",VLOOKUP(B2,'Mileage Rate Lookup'!$B$2:$D$3,3,TRUE),0)

Open in new window

Regards
0
 
pony10usCommented:
or:

=IF(D2="Mileage",(IF(B2<'Mileage Rate Lookup'!$B$3,'Mileage Rate Lookup'!$D$2,'Mileage Rate Lookup'!$D$3)))

Open in new window

0
 
rogerdjrAuthor Commented:
With a slight modification:

=IF(D2="Mileage",VLOOKUP(B2,'Mileage Rate Lookup'!$B:$D,3,TRUE),0)

This works great and allows for addition of newer mileage rates without any update.

Thanks
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now