Looking up and matching multiple values (one of which is a date within a range)

[DeeJP]

I'm creating a spreadsheet for tracking our business travel expenses and per diem allowances.

I have one sheet (2015) for the current year and trips, start date, end date, city, county. There is another sheet that lists all the counties in the US and per diem rates for lodging and meals and expenses. The lodging rates for some locations change depending on time of year (peak season etc) but not all.

So on one sheet (2015), based on the city in one cell it pulls in the county (this works fine). Based on the county it pulls in the rate for both lodging and meals (using INDEX MATCH). However, this is where I get lost. Some counties have up to three rates for lodging for different ate ranges. How can I look up the county and then depending on what range the date falls between, pull in the rate on that row?

This is my first post here. Let me know (in basic terms) what else you need to make sense of this. I'm not an excel beginner but I'm definitely not far past novice.

 

[DILIPandey]

Hi DeeJp,

Please post a sample of your data.
You can simply copy paste it here directly from your excel sheet.


Regards,
DILIPandey

 

[DeeJP]

[TABLE="width: 1133"]
<colgroup><col span="2"><col><col><col><col><col><col span="3"><col><col span="3"></colgroup><tbody>[TR]
[TD]
STARTING DATE
[/TD]
[TD]ENDING DATE[/TD]
[TD]EVENT NAME[/TD]
[TD]LOCATION[/TD]
[TD]CITY[/TD]
[TD]COUNTY[/TD]
[TD] [/TD]
[TD]PER DIEM RATE: meals & IE[/TD]
[TD]# of Days of Event[/TD]
[TD]Total Per Diem[/TD]
[TD] [/TD]
[TD]PER DIEM RATE: lodging[/TD]
[TD]PER DIEM RATE: lodging[/TD]
[TD]PER DIEM RATE: lodging[/TD]
[/TR]
[TR]
[TD]08/20/15 - 08/22/15[/TD]
[TD]22-Aug[/TD]
[TD]SAMPLE Event[/TD]
[TD]Orlando, FL[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]$50.00[/TD]
[TD]3[/TD]
[TD]$150.00[/TD]
[TD] [/TD]
[TD]$85.00[/TD]
[TD]3[/TD]
[TD]$255.00[/TD]
[/TR]
[TR]
[TD]1-Jan[/TD]
[TD]4-Jan[/TD]
[TD]Tampa Home Show[/TD]
[TD]Florida State Fairgrounds, 4800 US 301, Tampa, FL 33610[/TD]
[TD]Tampa[/TD]
[TD]Pinellas[/TD]
[TD] [/TD]
[TD]$51.00[/TD]
[TD]4[/TD]
[TD]$204.00[/TD]
[TD] [/TD]
[TD]$115.00[/TD]
[TD]3[/TD]
[TD]$345.00[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 981"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]STATE[/TD]
[TD]DESTINATION[/TD]
[TD]COUNTY / LOCATION DEFINED[/TD]
[TD]SEASON BEGIN[/TD]
[TD]SEASON END[/TD]
[TD]FY2015 Lodging Rate[/TD]
[TD]FY2015 M&IE[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD]Standard CONUS rate applies to all counties not specifically listed. Cities not listed may be located in a listed county.[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]$83[/TD]
[TD="align: right"]$46[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]AL[/TD]
[TD]Birmingham[/TD]
[TD]Jefferson / Shelby[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]$ 92[/TD]
[TD]$ 56[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]AL[/TD]
[TD]Gulf Shores[/TD]
[TD]Baldwin[/TD]
[TD]October 1[/TD]
[TD]February 28[/TD]
[TD]$ 100[/TD]
[TD]$ 51[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]AL[/TD]
[TD]Gulf Shores[/TD]
[TD]Baldwin[/TD]
[TD]March 1[/TD]
[TD]July 31[/TD]
[TD]$ 128[/TD]
[TD]$ 51[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]AL[/TD]
[TD]Gulf Shores[/TD]
[TD]Baldwin[/TD]
[TD]August 1[/TD]
[TD]September 30[/TD]
[TD]$ 100[/TD]
[TD]$ 51[/TD]
[/TR]
</tbody>[/TABLE]



The first lot of data is the sheet where I am collating the info. The second is where I am looking up the dates and rates.

Thanks!

 

[DeeJP]

I forgot to mention. If the county doesn't match a county in the list then the standard rate is pulled in. That all works fine. It's just the matching the county and the date range and then pulling in the relevant amount.

 

[DILIPandey]

Some counties have up to three rates for lodging for different ate ranges. How can I look up the county and then depending on what range the date falls between, pull in the rate on that row?

Is this case covered in your sample data ?
I understand that country name is the key filed in both the tables to pull the data but need to see a relevant sample data. Thanks.


Regards,
DILIPandey

 

[DeeJP]

[TABLE="width: 981"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]95
[/TD]
[TD]FL[/TD]
[TD]Miami[/TD]
[TD]Miami-Dade[/TD]
[TD]October 1[/TD]
[TD]December 31[/TD]
[TD]$ 152[/TD]
[TD]$ 66[/TD]
[/TR]
[TR]
[TD]95
[/TD]
[TD]FL[/TD]
[TD]Miami[/TD]
[TD]Miami-Dade[/TD]
[TD]January 1[/TD]
[TD]March 31[/TD]
[TD]$ 203[/TD]
[TD]$ 66[/TD]
[/TR]
[TR]
[TD]95[/TD]
[TD]FL[/TD]
[TD]Miami
[/TD]
[TD]Miami-Dade[/TD]
[TD]April 1[/TD]
[TD]May 31[/TD]
[TD]$ 146[/TD]
[TD]$ 66[/TD]
[/TR]
[TR]
[TD]95[/TD]
[TD]FL[/TD]
[TD]Miami
[/TD]
[TD]Miami-Dade[/TD]
[TD]June 1[/TD]
[TD]September 30[/TD]
[TD]$ 119[/TD]
[TD]$ 66[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 1133"]
<colgroup><col span="2"><col><col><col><col><col><col span="3"><col><col span="3"></colgroup><tbody>[TR]
[TD]2-Dec
[/TD]
[TD]4-Dec[/TD]
[TD]Miami presentation[/TD]
[TD]Miami fl [/TD]
[TD]Miami[/TD]
[TD]Miami-Dade[/TD]
[TD] [/TD]
[TD]$66.00[/TD]
[TD]3[/TD]
[TD]$198.00[/TD]
[TD] [/TD]
[TD]$152.00[/TD]
[TD]2[/TD]
[TD]$304.00[/TD]
[/TR]
</tbody>[/TABLE]


Is that better? So I need to pull in the data in the 10th column in the second lot of data based on the date in column A. In this instance it works but if the date was June 1 I need to pull $119 instead.

The file is here. https://www.dropbox.com/s/uz2gl3ubqobiimg/Travel Expenses - Jason Lucas.xlsx?dl=0

Thanks so much for your help!

 

[DILIPandey]

Okay.. I got the excel sheet from drop box.
Now 10th column in sheet "2015" is column J (Total per Diem).. correct ?

Can you give me an example now ?


Regards,
DILIPandey

 

[DeeJP]

Sure.

So on 2015 I enter the details of the trip. The day we left and the day we returned. The number in column H is based on the county in column F. So if the county is "Brevard" it looks on sheet '2015 Per diem rates', and pulls in $105 from cell G145. The problem is when the county in column F is, for example, Miami-Dade. Miami-Dade appears in rows 170-173 on '2015 Per Diem Rates'. There are many other counties that also appear more than once. The lodging rate depends on the date. For Miami0Dade the rates are as follows.

[TABLE="width: 342"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]October 1[/TD]
[TD]December 31[/TD]
[TD]$ 152[/TD]
[/TR]
[TR]
[TD]January 1[/TD]
[TD]March 31[/TD]
[TD]$ 203[/TD]
[/TR]
[TR]
[TD]April 1[/TD]
[TD]May 31[/TD]
[TD]$ 146[/TD]
[/TR]
[TR]
[TD]June 1[/TD]
[TD]September 30[/TD]
[TD]$ 119[/TD]
[/TR]
</tbody>[/TABLE]

So I need to first find the matching county but here there is 4 date ranges. I then need data from column G on '2015 Per Diem Rates' based on what range the date (column A on 2015) falls between.

Does that make sense?

 

[DILIPandey]

In H42 of 2015 sheet, enter below formula :-

=MIN(IFERROR(IF((MONTH('2015 Per Diem Rates'!$E$4:$E$654)<=MONTH('2015'!$A42))*(MONTH('2015 Per Diem Rates'!$F$4:$F$654)>=MONTH('2015'!$A42))*('2015 Per Diem Rates'!$D$4:$D$654='2015'!$F42),'2015 Per Diem Rates'!$G$4:$G$654,""),""))

Select cell H42, press f2, press key combination ctrl shift enter.
Now copy the formula in column H to fit your need.
Now on sheet "2015 Per Diem Rates", you need to have the columns E and F in such a way that column E is less than column F.
for example, if column E contains October 1 then column F will have December 31 not January 31.


Regards,
DILIPandey