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]

STARTING DATE

ENDING DATE

EVENT NAME

LOCATION

CITY

COUNTY

PER DIEM RATE: meals & IE

# of Days of Event

Total Per Diem

PER DIEM RATE: lodging

PER DIEM RATE: lodging

PER DIEM RATE: lodging

08/20/15 - 08/22/15

22-Aug

SAMPLE Event

Orlando, FL

$50.00

3

$150.00

$85.00

3

$255.00

1-Jan

4-Jan

Tampa Home Show

Florida State Fairgrounds, 4800 US 301, Tampa, FL 33610

Tampa

Pinellas

$51.00

4

$204.00

$115.00

3

$345.00

<colgroup><col span="2"><col><col><col><col><col><col span="3"><col><col span="3"></colgroup><tbody>
</tbody>

ID	STATE	DESTINATION	COUNTY / LOCATION DEFINED	SEASON BEGIN	SEASON END	FY2015 Lodging Rate	FY2015 M&IE
		Standard CONUS rate applies to all counties not specifically listed. Cities not listed may be located in a listed county.				$83	$46
1	AL	Birmingham	Jefferson / Shelby			$ 92	$ 56
2	AL	Gulf Shores	Baldwin	October 1	February 28	$ 100	$ 51
2	AL	Gulf Shores	Baldwin	March 1	July 31	$ 128	$ 51
2	AL	Gulf Shores	Baldwin	August 1	September 30	$ 100	$ 51

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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]

95	FL	Miami	Miami-Dade	October 1	December 31	$ 152	$ 66
95	FL	Miami	Miami-Dade	January 1	March 31	$ 203	$ 66
95	FL	Miami	Miami-Dade	April 1	May 31	$ 146	$ 66
95	FL	Miami	Miami-Dade	June 1	September 30	$ 119	$ 66

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

2-Dec

4-Dec

Miami presentation

Miami fl

Miami

Miami-Dade

$66.00

3

$198.00

$152.00

2

$304.00

<colgroup><col span="2"><col><col><col><col><col><col span="3"><col><col span="3"></colgroup><tbody>
</tbody>

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.

October 1	December 31	$ 152
January 1	March 31	$ 203
April 1	May 31	$ 146
June 1	September 30	$ 119

<colgroup><col><col><col></colgroup><tbody>
</tbody>

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