Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Lookup Value based on Date Span

Posted on 2014-01-03
Medium Priority
278 Views
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
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

LVL 52

Accepted Solution

Rgonzo1971 earned 2000 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)
``````
Regards
0

LVL 26

Expert Comment

ID: 39754251
or:

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

Author Closing Comment

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

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
###### Suggested Courses
Course of the Month9 days, 9 hours left to enroll