Solved

Lookup Value based on Date Span

Posted on 2014-01-03
3
256 Views
Last Modified: 2014-01-03
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
Comment
Question by:rogerdjr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39754229
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
 
LVL 26

Expert Comment

by:pony10us
ID: 39754251
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
 

Author Closing Comment

by:rogerdjr
ID: 39754429
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question