Nested IF or VLOOKUP based on 2 IFs: Month & Location

B

Bear Books

New Member
Joined
Oct 25, 2013
Messages
5
Hi, I am putting together a Per Diem Excel Worksheet that should, once working properly, calculate the correct rate to reimburse our employees for their travel. I am willing to share the results once completed. The Meal rate is very easy because it is solely based on location. The meals rate stays the same all year round. On the other hand, the lodging rates are based on location and month of the year. I have the source tables set in another tab. They are as follow: Column A: location; Column B: Meals rates; Column C through N: Lodging rates (Oct - Sept). Rows 3 - 59: locations where we travel to. The form itself is also very simple: the employee enters in each column: Client, Project, Task, Travel Date, Location. Then the form auto-calculates the rate based on Month and location. As I mentioned earlier I can do the VLOOKUP for the meals based only on the location very easily. Now, adding the month parameter ads a bit of a conundrum for me. Maybe some IF & AND should be used. Really not sure. Thanks for your help gals & guys.
 
Sort by date Sort by votes
Bear Books said:
Hi, I am putting together a Per Diem Excel Worksheet that should, once working properly, calculate the correct rate to reimburse our employees for their travel. I am willing to share the results once completed. The Meal rate is very easy because it is solely based on location. The meals rate stays the same all year round. On the other hand, the lodging rates are based on location and month of the year. I have the source tables set in another tab. They are as follow: Column A: location; Column B: Meals rates; Column C through N: Lodging rates (Oct - Sept). Rows 3 - 59: locations where we travel to. The form itself is also very simple: the employee enters in each column: Client, Project, Task, Travel Date, Location. Then the form auto-calculates the rate based on Month and location. As I mentioned earlier I can do the VLOOKUP for the meals based only on the location very easily. Now, adding the month parameter ads a bit of a conundrum for me. Maybe some IF & AND should be used. Really not sure. Thanks for your help gals & guys.
Click to expand...

Not sure if this would help.
Not knowing what formulae you generated and what version of Excel you are using.
Given in A2 the description as stated above:
<style>table { }td { padding: 0px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }</style> [TABLE="width: 910"]
<tbody>[TR]
[TD="width: 65"]location[/TD]
[TD="width: 65"]meals rate[/TD]
[TD="width: 65, align: right"]1[/TD]
[TD="width: 65, align: right"]2[/TD]
[TD="width: 65, align: right"]3[/TD]
[TD="width: 65, align: right"]4[/TD]
[TD="width: 65, align: right"]5[/TD]
[TD="width: 65, align: right"]6[/TD]
[TD="width: 65, align: right"]7[/TD]
[TD="width: 65, align: right"]8[/TD]
[TD="width: 65, align: right"]9[/TD]
[TD="width: 65, align: right"]10[/TD]
[TD="width: 65, align: right"]11[/TD]
[TD="width: 65, align: right"]12[/TD]
[/TR]
[TR]
[TD]Montgomery[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]52[/TD]
[/TR]
[TR]
[TD]Juneau[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]47[/TD]
[/TR]
[TR]
[TD]Phoenix[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]47[/TD]
[/TR]
[TR]
[TD]Little Rock[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]47[/TD]
[/TR]
[TR]
[TD]Sacramento[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]42[/TD]
[/TR]
[TR]
[TD]Denver[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]52[/TD]
[/TR]
[TR]
[TD]Hartford[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]52[/TD]
[/TR]
[TR]
[TD]Dover[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]52[/TD]
[/TR]
[TR]
[TD]Tallahassee[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]47[/TD]
[/TR]
[TR]
[TD]Atlanta[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]52[/TD]
[/TR]
[TR]
[TD]Honolulu[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]62[/TD]
[/TR]
[TR]
[TD]Boise[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]47[/TD]
[/TR]
[TR]
[TD]Springfield[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]52[/TD]
[/TR]
[TR]
[TD]Indianapolis[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]62[/TD]
[/TR]
[TR]
[TD]Des Moines[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]52[/TD]
[/TR]
[TR]
[TD]Topeka[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]57[/TD]
[/TR]
[TR]
[TD]Frankfort[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]52[/TD]
[/TR]
[TR]
[TD]Baton Rouge[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]42[/TD]
[/TR]
</tbody>[/TABLE]


<style>table { }td { padding: 0px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl64 { text-align: center; }</style> [TABLE="width: 195"]
<tbody>[TR]
[TD="class: xl64, width: 65"][/TD]
[TD="class: xl64, width: 65"][/TD]
[TD="class: xl64, width: 65"]Rate[/TD]
[/TR]
[TR]
[TD="class: xl64"]Location:[/TD]
[TD="class: xl64"]Hartford[/TD]
[TD="class: xl64"]22[/TD]
[/TR]
[TR]
[TD="class: xl64"]Month:[/TD]
[TD="class: xl64"]5[/TD]
[TD="class: xl64"]38[/TD]
[/TR]
</tbody>[/TABLE]

Formula used for Location Rate is =INDEX($B$3:$B$20,MATCH(B27,$A$3:$A$20,0))
Formula used for Month Rate is =LOOKUP(2,1/($A$3:$A$20=$B27),INDEX($C$3:$N$20,0,MATCH(B$28,$C$2:$N$2,1)))
Where B28 is the month value (here 5), B27 is the location (here Hartford).

Would that be close to what you are thinking of?
 
Last edited:
Upvote 0
Wow, that looks pretty close. I will try the formula and see if it gets me the desired result. I'll get back to you early next week. Thanks a bunch.
cyrilbrd said:
Not sure if this would help.
Not knowing what formulae you generated and what version of Excel you are using.
Given in A2 the description as stated above:
<style>table { }td { padding: 0px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }</style> [TABLE="width: 910"]
<tbody>[TR]
[TD="width: 65"]location
[/TD]
[TD="width: 65"]meals rate
[/TD]
[TD="width: 65, align: right"]1
[/TD]
[TD="width: 65, align: right"]2
[/TD]
[TD="width: 65, align: right"]3
[/TD]
[TD="width: 65, align: right"]4
[/TD]
[TD="width: 65, align: right"]5
[/TD]
[TD="width: 65, align: right"]6
[/TD]
[TD="width: 65, align: right"]7
[/TD]
[TD="width: 65, align: right"]8
[/TD]
[TD="width: 65, align: right"]9
[/TD]
[TD="width: 65, align: right"]10
[/TD]
[TD="width: 65, align: right"]11
[/TD]
[TD="width: 65, align: right"]12
[/TD]
[/TR]
[TR]
[TD]Montgomery
[/TD]
[TD="align: right"]24
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]32
[/TD]
[TD="align: right"]34
[/TD]
[TD="align: right"]36
[/TD]
[TD="align: right"]38
[/TD]
[TD="align: right"]40
[/TD]
[TD="align: right"]42
[/TD]
[TD="align: right"]44
[/TD]
[TD="align: right"]46
[/TD]
[TD="align: right"]48
[/TD]
[TD="align: right"]50
[/TD]
[TD="align: right"]52
[/TD]
[/TR]
[TR]
[TD]Juneau
[/TD]
[TD="align: right"]44
[/TD]
[TD="align: right"]25
[/TD]
[TD="align: right"]27
[/TD]
[TD="align: right"]29
[/TD]
[TD="align: right"]31
[/TD]
[TD="align: right"]33
[/TD]
[TD="align: right"]35
[/TD]
[TD="align: right"]37
[/TD]
[TD="align: right"]39
[/TD]
[TD="align: right"]41
[/TD]
[TD="align: right"]43
[/TD]
[TD="align: right"]45
[/TD]
[TD="align: right"]47
[/TD]
[/TR]
[TR]
[TD]Phoenix
[/TD]
[TD="align: right"]46
[/TD]
[TD="align: right"]25
[/TD]
[TD="align: right"]27
[/TD]
[TD="align: right"]29
[/TD]
[TD="align: right"]31
[/TD]
[TD="align: right"]33
[/TD]
[TD="align: right"]35
[/TD]
[TD="align: right"]37
[/TD]
[TD="align: right"]39
[/TD]
[TD="align: right"]41
[/TD]
[TD="align: right"]43
[/TD]
[TD="align: right"]45
[/TD]
[TD="align: right"]47
[/TD]
[/TR]
[TR]
[TD]Little Rock
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]25
[/TD]
[TD="align: right"]27
[/TD]
[TD="align: right"]29
[/TD]
[TD="align: right"]31
[/TD]
[TD="align: right"]33
[/TD]
[TD="align: right"]35
[/TD]
[TD="align: right"]37
[/TD]
[TD="align: right"]39
[/TD]
[TD="align: right"]41
[/TD]
[TD="align: right"]43
[/TD]
[TD="align: right"]45
[/TD]
[TD="align: right"]47
[/TD]
[/TR]
[TR]
[TD]Sacramento
[/TD]
[TD="align: right"]42
[/TD]
[TD="align: right"]20
[/TD]
[TD="align: right"]22
[/TD]
[TD="align: right"]24
[/TD]
[TD="align: right"]26
[/TD]
[TD="align: right"]28
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]32
[/TD]
[TD="align: right"]34
[/TD]
[TD="align: right"]36
[/TD]
[TD="align: right"]38
[/TD]
[TD="align: right"]40
[/TD]
[TD="align: right"]42
[/TD]
[/TR]
[TR]
[TD]Denver
[/TD]
[TD="align: right"]24
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]32
[/TD]
[TD="align: right"]34
[/TD]
[TD="align: right"]36
[/TD]
[TD="align: right"]38
[/TD]
[TD="align: right"]40
[/TD]
[TD="align: right"]42
[/TD]
[TD="align: right"]44
[/TD]
[TD="align: right"]46
[/TD]
[TD="align: right"]48
[/TD]
[TD="align: right"]50
[/TD]
[TD="align: right"]52
[/TD]
[/TR]
[TR]
[TD]Hartford
[/TD]
[TD="align: right"]22
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]32
[/TD]
[TD="align: right"]34
[/TD]
[TD="align: right"]36
[/TD]
[TD="align: right"]38
[/TD]
[TD="align: right"]40
[/TD]
[TD="align: right"]42
[/TD]
[TD="align: right"]43
[/TD]
[TD="align: right"]46
[/TD]
[TD="align: right"]48
[/TD]
[TD="align: right"]50
[/TD]
[TD="align: right"]52
[/TD]
[/TR]
[TR]
[TD]Dover
[/TD]
[TD="align: right"]42
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]32
[/TD]
[TD="align: right"]34
[/TD]
[TD="align: right"]36
[/TD]
[TD="align: right"]38
[/TD]
[TD="align: right"]40
[/TD]
[TD="align: right"]42
[/TD]
[TD="align: right"]44
[/TD]
[TD="align: right"]46
[/TD]
[TD="align: right"]48
[/TD]
[TD="align: right"]50
[/TD]
[TD="align: right"]52
[/TD]
[/TR]
[TR]
[TD]Tallahassee
[/TD]
[TD="align: right"]37
[/TD]
[TD="align: right"]25
[/TD]
[TD="align: right"]27
[/TD]
[TD="align: right"]29
[/TD]
[TD="align: right"]31
[/TD]
[TD="align: right"]33
[/TD]
[TD="align: right"]35
[/TD]
[TD="align: right"]37
[/TD]
[TD="align: right"]39
[/TD]
[TD="align: right"]41
[/TD]
[TD="align: right"]43
[/TD]
[TD="align: right"]45
[/TD]
[TD="align: right"]47
[/TD]
[/TR]
[TR]
[TD]Atlanta
[/TD]
[TD="align: right"]43
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]32
[/TD]
[TD="align: right"]34
[/TD]
[TD="align: right"]36
[/TD]
[TD="align: right"]38
[/TD]
[TD="align: right"]40
[/TD]
[TD="align: right"]42
[/TD]
[TD="align: right"]44
[/TD]
[TD="align: right"]46
[/TD]
[TD="align: right"]48
[/TD]
[TD="align: right"]50
[/TD]
[TD="align: right"]52
[/TD]
[/TR]
[TR]
[TD]Honolulu
[/TD]
[TD="align: right"]40
[/TD]
[TD="align: right"]40
[/TD]
[TD="align: right"]42
[/TD]
[TD="align: right"]44
[/TD]
[TD="align: right"]46
[/TD]
[TD="align: right"]48
[/TD]
[TD="align: right"]50
[/TD]
[TD="align: right"]52
[/TD]
[TD="align: right"]54
[/TD]
[TD="align: right"]56
[/TD]
[TD="align: right"]58
[/TD]
[TD="align: right"]60
[/TD]
[TD="align: right"]62
[/TD]
[/TR]
[TR]
[TD]Boise
[/TD]
[TD="align: right"]40
[/TD]
[TD="align: right"]25
[/TD]
[TD="align: right"]27
[/TD]
[TD="align: right"]29
[/TD]
[TD="align: right"]31
[/TD]
[TD="align: right"]33
[/TD]
[TD="align: right"]35
[/TD]
[TD="align: right"]37
[/TD]
[TD="align: right"]39
[/TD]
[TD="align: right"]41
[/TD]
[TD="align: right"]43
[/TD]
[TD="align: right"]45
[/TD]
[TD="align: right"]47
[/TD]
[/TR]
[TR]
[TD]Springfield
[/TD]
[TD="align: right"]34
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]32
[/TD]
[TD="align: right"]34
[/TD]
[TD="align: right"]36
[/TD]
[TD="align: right"]38
[/TD]
[TD="align: right"]40
[/TD]
[TD="align: right"]42
[/TD]
[TD="align: right"]44
[/TD]
[TD="align: right"]46
[/TD]
[TD="align: right"]48
[/TD]
[TD="align: right"]50
[/TD]
[TD="align: right"]52
[/TD]
[/TR]
[TR]
[TD]Indianapolis
[/TD]
[TD="align: right"]33
[/TD]
[TD="align: right"]40
[/TD]
[TD="align: right"]42
[/TD]
[TD="align: right"]44
[/TD]
[TD="align: right"]46
[/TD]
[TD="align: right"]48
[/TD]
[TD="align: right"]50
[/TD]
[TD="align: right"]52
[/TD]
[TD="align: right"]54
[/TD]
[TD="align: right"]56
[/TD]
[TD="align: right"]58
[/TD]
[TD="align: right"]60
[/TD]
[TD="align: right"]62
[/TD]
[/TR]
[TR]
[TD]Des Moines
[/TD]
[TD="align: right"]43
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]32
[/TD]
[TD="align: right"]34
[/TD]
[TD="align: right"]36
[/TD]
[TD="align: right"]38
[/TD]
[TD="align: right"]40
[/TD]
[TD="align: right"]42
[/TD]
[TD="align: right"]44
[/TD]
[TD="align: right"]46
[/TD]
[TD="align: right"]48
[/TD]
[TD="align: right"]50
[/TD]
[TD="align: right"]52
[/TD]
[/TR]
[TR]
[TD]Topeka
[/TD]
[TD="align: right"]27
[/TD]
[TD="align: right"]35
[/TD]
[TD="align: right"]37
[/TD]
[TD="align: right"]39
[/TD]
[TD="align: right"]41
[/TD]
[TD="align: right"]43
[/TD]
[TD="align: right"]45
[/TD]
[TD="align: right"]47
[/TD]
[TD="align: right"]49
[/TD]
[TD="align: right"]51
[/TD]
[TD="align: right"]53
[/TD]
[TD="align: right"]55
[/TD]
[TD="align: right"]57
[/TD]
[/TR]
[TR]
[TD]Frankfort
[/TD]
[TD="align: right"]50
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]32
[/TD]
[TD="align: right"]34
[/TD]
[TD="align: right"]36
[/TD]
[TD="align: right"]38
[/TD]
[TD="align: right"]40
[/TD]
[TD="align: right"]42
[/TD]
[TD="align: right"]44
[/TD]
[TD="align: right"]46
[/TD]
[TD="align: right"]48
[/TD]
[TD="align: right"]50
[/TD]
[TD="align: right"]52
[/TD]
[/TR]
[TR]
[TD]Baton Rouge
[/TD]
[TD="align: right"]34
[/TD]
[TD="align: right"]20
[/TD]
[TD="align: right"]22
[/TD]
[TD="align: right"]24
[/TD]
[TD="align: right"]26
[/TD]
[TD="align: right"]28
[/TD]
[TD="align: right"]31
[/TD]
[TD="align: right"]32
[/TD]
[TD="align: right"]34
[/TD]
[TD="align: right"]36
[/TD]
[TD="align: right"]38
[/TD]
[TD="align: right"]40
[/TD]
[TD="align: right"]42
[/TD]
[/TR]
</tbody>[/TABLE]


<style>table { }td { padding: 0px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl64 { text-align: center; }</style> [TABLE="width: 195"]
<tbody>[TR]
[TD="class: xl64, width: 65"][/TD]
[TD="class: xl64, width: 65"][/TD]
[TD="class: xl64, width: 65"]Rate
[/TD]
[/TR]
[TR]
[TD="class: xl64"]Location:
[/TD]
[TD="class: xl64"]Hartford
[/TD]
[TD="class: xl64"]22
[/TD]
[/TR]
[TR]
[TD="class: xl64"]Month:
[/TD]
[TD="class: xl64"]5
[/TD]
[TD="class: xl64"]38
[/TD]
[/TR]
</tbody>[/TABLE]

Formula used for Location Rate is =INDEX($B$3:$B$20,MATCH(B27,$A$3:$A$20,0))
Formula used for Month Rate is =LOOKUP(2,1/($A$3:$A$20=$B27),INDEX($C$3:$N$20,0,MATCH(B$28,$C$2:$N$2,1)))
Where B28 is the month value (here 5), B27 is the location (here Hartford).

Would that be close to what you are thinking of?
Click to expand...
 
Upvote 0
Bear Books said:
Wow, that looks pretty close. I will try the formula and see if it gets me the desired result. I'll get back to you early next week. Thanks a bunch.
Click to expand...

No problem, have a nice long weekend.
 
Upvote 0
I am going to try to post below the actual "per diem table" I put together with the info gathered from the GSA website. As you can see, each location has its own meal rate good for the whole year. That an easy LOOKUP query. On the other hand, the Lodging rate evolves for about 20 locations depending on the time of the year. (probably due to the tourist season, etc.) I will post the query form in the next post to clarify the whole thing. On a side issue, how come I cannot enable a new paragraph while typing this? When I hit the Enter key, nothing happens. Am I missing something?[TABLE="width: 966"]
<tbody>[TR]
[TD="class: xl64, width: 421"]Locations
[/TD]
[TD="class: xl66, width: 84"]Per Diem
Meals
[/TD]
[TD="class: xl65, width: 78"]Lodging: Oct
[/TD]
[TD="class: xl64, width: 64"] Nov
[/TD]
[TD="class: xl65, width: 64"] Dec
[/TD]
[TD="class: xl64, width: 64"] Jan
[/TD]
[TD="class: xl65, width: 64"] Feb
[/TD]
[TD="class: xl64, width: 64"] Mar
[/TD]
[TD="class: xl65, width: 64"] Apr
[/TD]
[TD="class: xl64, width: 64"] May
[/TD]
[TD="class: xl65, width: 64"] Jun
[/TD]
[TD="class: xl64, width: 64"] Jul
[/TD]
[TD="class: xl65, width: 64"] Aug
[/TD]
[TD="class: xl64, width: 64"] Sep
[/TD]
[/TR]
[TR]
[TD]OREGON Standard Rate
[/TD]
[TD="class: xl71, width: 84"]46
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421, bgcolor: transparent"]Beaverton, Washington Co
[/TD]
[TD="class: xl71, width: 84, bgcolor: transparent"]51
[/TD]
[TD="class: xl70, width: 78, bgcolor: transparent, align: right"]106
[/TD]
[TD="class: xl70, width: 64, bgcolor: transparent, align: right"]106
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]106
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]106
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]106
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]106
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]106
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]106
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]106
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]106
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]106
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]106
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421"]Bend, Deschutes Co
[/TD]
[TD="class: xl71, width: 84"]61
[/TD]
[TD="class: xl70, width: 78, align: right"]89
[/TD]
[TD="class: xl70, width: 64, align: right"]89
[/TD]
[TD="class: xl69, align: right"]89
[/TD]
[TD="class: xl69, align: right"]89
[/TD]
[TD="class: xl69, align: right"]89
[/TD]
[TD="class: xl69, align: right"]89
[/TD]
[TD="class: xl69, align: right"]89
[/TD]
[TD="class: xl69, align: right"]89
[/TD]
[TD="class: xl69, align: right"]89
[/TD]
[TD="class: xl69, align: right"]107
[/TD]
[TD="class: xl69, align: right"]107
[/TD]
[TD="class: xl69, align: right"]89
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421, bgcolor: transparent"]Clackamas, Clackamas Co
[/TD]
[TD="class: xl71, width: 84, bgcolor: transparent"]61
[/TD]
[TD="class: xl70, width: 78, bgcolor: transparent, align: right"]90
[/TD]
[TD="class: xl70, width: 64, bgcolor: transparent, align: right"]90
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]90
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]90
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]90
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]90
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]90
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]90
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]90
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]90
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]90
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]90
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421"]Eugene / Florence, Lane Co
[/TD]
[TD="class: xl71, width: 84"]51
[/TD]
[TD="class: xl70, width: 78, align: right"]94
[/TD]
[TD="class: xl70, width: 64, align: right"]94
[/TD]
[TD="class: xl69, align: right"]94
[/TD]
[TD="class: xl69, align: right"]94
[/TD]
[TD="class: xl69, align: right"]94
[/TD]
[TD="class: xl69, align: right"]94
[/TD]
[TD="class: xl69, align: right"]94
[/TD]
[TD="class: xl69, align: right"]94
[/TD]
[TD="class: xl69, align: right"]94
[/TD]
[TD="class: xl69, align: right"]94
[/TD]
[TD="class: xl69, align: right"]94
[/TD]
[TD="class: xl69, align: right"]94
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421, bgcolor: transparent"]Lincoln City, Lincoln Co
[/TD]
[TD="class: xl71, width: 84, bgcolor: transparent"]56
[/TD]
[TD="class: xl70, width: 78, bgcolor: transparent, align: right"]94
[/TD]
[TD="class: xl70, width: 64, bgcolor: transparent, align: right"]94
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]94
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]94
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]94
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]94
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]94
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]94
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]94
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]121
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]121
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]94
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421"]Portland, Multnomah Co
[/TD]
[TD="class: xl71, width: 84"]66
[/TD]
[TD="class: xl70, width: 78, align: right"]126
[/TD]
[TD="class: xl70, width: 64, align: right"]126
[/TD]
[TD="class: xl69, align: right"]126
[/TD]
[TD="class: xl69, align: right"]126
[/TD]
[TD="class: xl69, align: right"]126
[/TD]
[TD="class: xl69, align: right"]126
[/TD]
[TD="class: xl69, align: right"]126
[/TD]
[TD="class: xl69, align: right"]126
[/TD]
[TD="class: xl69, align: right"]126
[/TD]
[TD="class: xl69, align: right"]126
[/TD]
[TD="class: xl69, align: right"]126
[/TD]
[TD="class: xl69, align: right"]126
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421, bgcolor: transparent"]Seaside, Clatsop Co
[/TD]
[TD="class: xl71, width: 84, bgcolor: transparent"]51
[/TD]
[TD="class: xl70, width: 78, bgcolor: transparent, align: right"]96
[/TD]
[TD="class: xl70, width: 64, bgcolor: transparent, align: right"]96
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]96
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]96
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]96
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]96
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]96
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]96
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]96
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]138
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]138
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]96
[/TD]
[/TR]
[TR]
[TD]WASHINGTON Standard Rate
[/TD]
[TD="class: xl72"]46
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421, bgcolor: transparent"]Anacortes / Coupeville / Oak Harbor, Skagit, Island, San Juan
[/TD]
[TD="class: xl73, width: 84, bgcolor: transparent"]61
[/TD]
[TD="class: xl70, width: 78, bgcolor: transparent, align: right"]91
[/TD]
[TD="class: xl70, width: 64, bgcolor: transparent, align: right"]91
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]91
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]91
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]91
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]91
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]91
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]91
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]91
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]91
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]91
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]91
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421"]Everett / Lynnwood, Snohomish Co
[/TD]
[TD="class: xl73, width: 84"]61
[/TD]
[TD="class: xl70, width: 78, align: right"]104
[/TD]
[TD="class: xl70, width: 64, align: right"]104
[/TD]
[TD="class: xl69, align: right"]104
[/TD]
[TD="class: xl69, align: right"]104
[/TD]
[TD="class: xl69, align: right"]104
[/TD]
[TD="class: xl69, align: right"]104
[/TD]
[TD="class: xl69, align: right"]104
[/TD]
[TD="class: xl69, align: right"]104
[/TD]
[TD="class: xl69, align: right"]104
[/TD]
[TD="class: xl69, align: right"]104
[/TD]
[TD="class: xl69, align: right"]104
[/TD]
[TD="class: xl69, align: right"]104
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421, bgcolor: transparent"]Ocean Shores, Grays Harbor Co
[/TD]
[TD="class: xl73, width: 84, bgcolor: transparent"]51
[/TD]
[TD="class: xl70, width: 78, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl70, width: 64, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]105
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]105
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421"]Olympia / Tumwater, Thurston Co
[/TD]
[TD="class: xl73, width: 84"]61
[/TD]
[TD="class: xl70, width: 78, align: right"]94
[/TD]
[TD="class: xl70, width: 64, align: right"]94
[/TD]
[TD="class: xl69, align: right"]94
[/TD]
[TD="class: xl69, align: right"]94
[/TD]
[TD="class: xl69, align: right"]94
[/TD]
[TD="class: xl69, align: right"]94
[/TD]
[TD="class: xl69, align: right"]94
[/TD]
[TD="class: xl69, align: right"]94
[/TD]
[TD="class: xl69, align: right"]94
[/TD]
[TD="class: xl69, align: right"]94
[/TD]
[TD="class: xl69, align: right"]94
[/TD]
[TD="class: xl69, align: right"]94
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421, bgcolor: transparent"]Port Angeles / Port Townsend, Clallam Co, Jefferson Co
[/TD]
[TD="class: xl73, width: 84, bgcolor: transparent"]61
[/TD]
[TD="class: xl70, width: 78, bgcolor: transparent, align: right"]95
[/TD]
[TD="class: xl70, width: 64, bgcolor: transparent, align: right"]95
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]95
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]95
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]95
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]95
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]95
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]95
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]95
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]122
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]122
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]95
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421"]Richland / Pasco, Benton Co, Franklin Co
[/TD]
[TD="class: xl73, width: 84"]46
[/TD]
[TD="class: xl70, width: 78, align: right"]90
[/TD]
[TD="class: xl70, width: 64, align: right"]90
[/TD]
[TD="class: xl69, align: right"]90
[/TD]
[TD="class: xl69, align: right"]90
[/TD]
[TD="class: xl69, align: right"]90
[/TD]
[TD="class: xl69, align: right"]90
[/TD]
[TD="class: xl69, align: right"]90
[/TD]
[TD="class: xl69, align: right"]90
[/TD]
[TD="class: xl69, align: right"]90
[/TD]
[TD="class: xl69, align: right"]90
[/TD]
[TD="class: xl69, align: right"]90
[/TD]
[TD="class: xl69, align: right"]90
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421, bgcolor: transparent"]Seattle, King Co
[/TD]
[TD="class: xl73, width: 84, bgcolor: transparent"]71
[/TD]
[TD="class: xl70, width: 78, bgcolor: transparent, align: right"]152
[/TD]
[TD="class: xl70, width: 64, bgcolor: transparent, align: right"]152
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]152
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]152
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]152
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]152
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]152
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]152
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]152
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]152
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]152
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]152
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421"]Spokane, Spokane Co
[/TD]
[TD="class: xl73, width: 84"]61
[/TD]
[TD="class: xl70, width: 78, align: right"]88
[/TD]
[TD="class: xl70, width: 64, align: right"]88
[/TD]
[TD="class: xl69, align: right"]88
[/TD]
[TD="class: xl69, align: right"]88
[/TD]
[TD="class: xl69, align: right"]88
[/TD]
[TD="class: xl69, align: right"]88
[/TD]
[TD="class: xl69, align: right"]88
[/TD]
[TD="class: xl69, align: right"]88
[/TD]
[TD="class: xl69, align: right"]88
[/TD]
[TD="class: xl69, align: right"]88
[/TD]
[TD="class: xl69, align: right"]88
[/TD]
[TD="class: xl69, align: right"]88
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421, bgcolor: transparent"]Tacoma, Pierce, Co
[/TD]
[TD="class: xl73, width: 84, bgcolor: transparent"]61
[/TD]
[TD="class: xl70, width: 78, bgcolor: transparent, align: right"]106
[/TD]
[TD="class: xl70, width: 64, bgcolor: transparent, align: right"]106
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]106
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]106
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]106
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]106
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]106
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]106
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]106
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]106
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]106
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]106
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421"]Vancouver, Clark Co, Cowlitz Co, Skamania Co
[/TD]
[TD="class: xl73, width: 84"]56
[/TD]
[TD="class: xl70, width: 78, align: right"]126
[/TD]
[TD="class: xl70, width: 64, align: right"]126
[/TD]
[TD="class: xl69, align: right"]126
[/TD]
[TD="class: xl69, align: right"]126
[/TD]
[TD="class: xl69, align: right"]126
[/TD]
[TD="class: xl69, align: right"]126
[/TD]
[TD="class: xl69, align: right"]126
[/TD]
[TD="class: xl69, align: right"]126
[/TD]
[TD="class: xl69, align: right"]126
[/TD]
[TD="class: xl69, align: right"]126
[/TD]
[TD="class: xl69, align: right"]126
[/TD]
[TD="class: xl69, align: right"]126
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421, bgcolor: transparent"]IDAHO Standard Rate
[/TD]
[TD="class: xl73, width: 84, bgcolor: transparent"]46
[/TD]
[TD="class: xl70, width: 78, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl70, width: 64, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421"]Bonner's Ferry / Sandpoint, Bonner Co, Boundary Co, Shoshone Co
[/TD]
[TD="class: xl73, width: 84"]61
[/TD]
[TD="class: xl70, width: 78, align: right"]83
[/TD]
[TD="class: xl70, width: 64, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]107
[/TD]
[TD="class: xl69, align: right"]107
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421, bgcolor: transparent"]Coeur d'Alene, Kootenai Co
[/TD]
[TD="class: xl73, width: 84, bgcolor: transparent"]61
[/TD]
[TD="class: xl70, width: 78, bgcolor: transparent, align: right"]85
[/TD]
[TD="class: xl70, width: 64, bgcolor: transparent, align: right"]85
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]85
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]85
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]85
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]85
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]85
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]85
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]126
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]126
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]126
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]85
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421"]Driggs / Idaho Falls, Bonneville Co, Fremont Co, Teton Co
[/TD]
[TD="class: xl73, width: 84"]46
[/TD]
[TD="class: xl70, width: 78, align: right"]84
[/TD]
[TD="class: xl70, width: 64, align: right"]84
[/TD]
[TD="class: xl69, align: right"]84
[/TD]
[TD="class: xl69, align: right"]84
[/TD]
[TD="class: xl69, align: right"]84
[/TD]
[TD="class: xl69, align: right"]84
[/TD]
[TD="class: xl69, align: right"]84
[/TD]
[TD="class: xl69, align: right"]84
[/TD]
[TD="class: xl69, align: right"]84
[/TD]
[TD="class: xl69, align: right"]84
[/TD]
[TD="class: xl69, align: right"]84
[/TD]
[TD="class: xl69, align: right"]84
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421, bgcolor: transparent"]Sun Valley / Ketchum, Blaine Co
[/TD]
[TD="class: xl73, width: 84, bgcolor: transparent"]71
[/TD]
[TD="class: xl70, width: 78, bgcolor: transparent, align: right"]93
[/TD]
[TD="class: xl70, width: 64, bgcolor: transparent, align: right"]93
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]93
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]93
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]93
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]93
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]93
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]93
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]93
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]116
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]116
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]93
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421"]MONTANA Standard Rate
[/TD]
[TD="class: xl73, width: 84"]46
[/TD]
[TD="class: xl70, width: 78, align: right"]83
[/TD]
[TD="class: xl70, width: 64, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421, bgcolor: transparent"]Big Sky / West Yellowstone, Gallatin Co
[/TD]
[TD="class: xl73, width: 84, bgcolor: transparent"]61
[/TD]
[TD="class: xl70, width: 78, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl70, width: 64, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]117
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]117
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]117
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]117
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421"]Butte, Silber Bow
[/TD]
[TD="class: xl73, width: 84"]51
[/TD]
[TD="class: xl70, width: 78, align: right"]88
[/TD]
[TD="class: xl70, width: 64, align: right"]88
[/TD]
[TD="class: xl69, align: right"]88
[/TD]
[TD="class: xl69, align: right"]88
[/TD]
[TD="class: xl69, align: right"]88
[/TD]
[TD="class: xl69, align: right"]88
[/TD]
[TD="class: xl69, align: right"]88
[/TD]
[TD="class: xl69, align: right"]88
[/TD]
[TD="class: xl69, align: right"]88
[/TD]
[TD="class: xl69, align: right"]88
[/TD]
[TD="class: xl69, align: right"]88
[/TD]
[TD="class: xl69, align: right"]88
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421, bgcolor: transparent"]Glendive / Sidney, Dawson Co, Richland Co
[/TD]
[TD="class: xl73, width: 84, bgcolor: transparent"]56
[/TD]
[TD="class: xl70, width: 78, bgcolor: transparent, align: right"]105
[/TD]
[TD="class: xl70, width: 64, bgcolor: transparent, align: right"]105
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]105
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]105
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]105
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]105
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]105
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]105
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]105
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]105
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]105
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]105
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421"]Helena, Lewis & Clark Co
[/TD]
[TD="class: xl73, width: 84"]56
[/TD]
[TD="class: xl70, width: 78, align: right"]88
[/TD]
[TD="class: xl70, width: 64, align: right"]88
[/TD]
[TD="class: xl69, align: right"]88
[/TD]
[TD="class: xl69, align: right"]88
[/TD]
[TD="class: xl69, align: right"]88
[/TD]
[TD="class: xl69, align: right"]88
[/TD]
[TD="class: xl69, align: right"]88
[/TD]
[TD="class: xl69, align: right"]88
[/TD]
[TD="class: xl69, align: right"]88
[/TD]
[TD="class: xl69, align: right"]88
[/TD]
[TD="class: xl69, align: right"]88
[/TD]
[TD="class: xl69, align: right"]88
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421, bgcolor: transparent"]Missoula / Polson / Kalispell, Missoula Co, Lake Co, Flathead Co
[/TD]
[TD="class: xl73, width: 84, bgcolor: transparent"]51
[/TD]
[TD="class: xl70, width: 78, bgcolor: transparent, align: right"]89
[/TD]
[TD="class: xl70, width: 64, bgcolor: transparent, align: right"]89
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]89
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]89
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]89
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]89
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]89
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]89
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]89
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]114
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]114
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]89
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421"]WYOMING Standard Rate
[/TD]
[TD="class: xl73, width: 84"]46
[/TD]
[TD="class: xl70, width: 78, align: right"]83
[/TD]
[TD="class: xl70, width: 64, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421, bgcolor: transparent"]Cody, Park Co
[/TD]
[TD="class: xl73, width: 84, bgcolor: transparent"]51
[/TD]
[TD="class: xl70, width: 78, bgcolor: transparent, align: right"]93
[/TD]
[TD="class: xl70, width: 64, bgcolor: transparent, align: right"]93
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]93
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]93
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]93
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]93
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]93
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]93
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]132
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]132
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]132
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]132
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421"]Evanston / Rock Springs, Sweetwater Co, Uinta Co
[/TD]
[TD="class: xl73, width: 84"]51
[/TD]
[TD="class: xl70, width: 78, align: right"]89
[/TD]
[TD="class: xl70, width: 64, align: right"]89
[/TD]
[TD="class: xl69, align: right"]89
[/TD]
[TD="class: xl69, align: right"]89
[/TD]
[TD="class: xl69, align: right"]89
[/TD]
[TD="class: xl69, align: right"]89
[/TD]
[TD="class: xl69, align: right"]89
[/TD]
[TD="class: xl69, align: right"]89
[/TD]
[TD="class: xl69, align: right"]89
[/TD]
[TD="class: xl69, align: right"]89
[/TD]
[TD="class: xl69, align: right"]89
[/TD]
[TD="class: xl69, align: right"]89
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421, bgcolor: transparent"]Gillette, Campbell Co
[/TD]
[TD="class: xl73, width: 84, bgcolor: transparent"]51
[/TD]
[TD="class: xl70, width: 78, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl70, width: 64, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]88
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]88
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]88
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421"]Jackson / Pinedale, Teton Co, Sublette Co
[/TD]
[TD="class: xl73, width: 84"]56
[/TD]
[TD="class: xl70, width: 78, align: right"]117
[/TD]
[TD="class: xl70, width: 64, align: right"]117
[/TD]
[TD="class: xl69, align: right"]117
[/TD]
[TD="class: xl69, align: right"]117
[/TD]
[TD="class: xl69, align: right"]117
[/TD]
[TD="class: xl69, align: right"]117
[/TD]
[TD="class: xl69, align: right"]117
[/TD]
[TD="class: xl69, align: right"]117
[/TD]
[TD="class: xl69, align: right"]117
[/TD]
[TD="class: xl69, align: right"]180
[/TD]
[TD="class: xl69, align: right"]180
[/TD]
[TD="class: xl69, align: right"]117
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421, bgcolor: transparent"]Sheridan, Sheridan Co
[/TD]
[TD="class: xl73, width: 84, bgcolor: transparent"]56
[/TD]
[TD="class: xl70, width: 78, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl70, width: 64, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]88
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]88
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]88
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421"]COLORADO Standard Rate
[/TD]
[TD="class: xl73, width: 84"]46
[/TD]
[TD="class: xl70, width: 78, align: right"]83
[/TD]
[TD="class: xl70, width: 64, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421, bgcolor: transparent"]Aspen, Pitkin Co
[/TD]
[TD="class: xl73, width: 84, bgcolor: transparent"]71
[/TD]
[TD="class: xl70, width: 78, bgcolor: transparent, align: right"]122
[/TD]
[TD="class: xl70, width: 64, bgcolor: transparent, align: right"]122
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]247
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]247
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]247
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]247
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]120
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]120
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]213
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]213
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]213
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]122
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421"]Boulder / Broomfield, Boulder Co, Broomfield Co
[/TD]
[TD="class: xl73, width: 84"]61
[/TD]
[TD="class: xl70, width: 78, align: right"]111
[/TD]
[TD="class: xl70, width: 64, align: right"]111
[/TD]
[TD="class: xl69, align: right"]111
[/TD]
[TD="class: xl69, align: right"]111
[/TD]
[TD="class: xl69, align: right"]111
[/TD]
[TD="class: xl69, align: right"]111
[/TD]
[TD="class: xl69, align: right"]111
[/TD]
[TD="class: xl69, align: right"]111
[/TD]
[TD="class: xl69, align: right"]111
[/TD]
[TD="class: xl69, align: right"]111
[/TD]
[TD="class: xl69, align: right"]111
[/TD]
[TD="class: xl69, align: right"]111
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421, bgcolor: transparent"]Colorado Springs, El Paso Co
[/TD]
[TD="class: xl73, width: 84, bgcolor: transparent"]66
[/TD]
[TD="class: xl70, width: 78, bgcolor: transparent, align: right"]87
[/TD]
[TD="class: xl70, width: 64, bgcolor: transparent, align: right"]87
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]87
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]87
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]87
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]87
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]87
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]87
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]87
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]87
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]87
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]87
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421"]Cortez, Montezuma Co
[/TD]
[TD="class: xl73, width: 84"]51
[/TD]
[TD="class: xl70, width: 78, align: right"]87
[/TD]
[TD="class: xl70, width: 64, align: right"]87
[/TD]
[TD="class: xl69, align: right"]87
[/TD]
[TD="class: xl69, align: right"]87
[/TD]
[TD="class: xl69, align: right"]87
[/TD]
[TD="class: xl69, align: right"]87
[/TD]
[TD="class: xl69, align: right"]87
[/TD]
[TD="class: xl69, align: right"]87
[/TD]
[TD="class: xl69, align: right"]109
[/TD]
[TD="class: xl69, align: right"]109
[/TD]
[TD="class: xl69, align: right"]109
[/TD]
[TD="class: xl69, align: right"]109
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421, bgcolor: transparent"]Crested Butte / Gunnison, Gunnison Co
[/TD]
[TD="class: xl73, width: 84, bgcolor: transparent"]51
[/TD]
[TD="class: xl70, width: 78, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl70, width: 64, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]94
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]94
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]94
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421"]Denver / Aurora, Denver Co, Adams Co, Arapahoe Co, Jefferson Co
[/TD]
[TD="class: xl73, width: 84"]66
[/TD]
[TD="class: xl70, width: 78, align: right"]156
[/TD]
[TD="class: xl70, width: 64, align: right"]156
[/TD]
[TD="class: xl69, align: right"]156
[/TD]
[TD="class: xl69, align: right"]156
[/TD]
[TD="class: xl69, align: right"]156
[/TD]
[TD="class: xl69, align: right"]156
[/TD]
[TD="class: xl69, align: right"]156
[/TD]
[TD="class: xl69, align: right"]156
[/TD]
[TD="class: xl69, align: right"]156
[/TD]
[TD="class: xl69, align: right"]156
[/TD]
[TD="class: xl69, align: right"]156
[/TD]
[TD="class: xl69, align: right"]156
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421, bgcolor: transparent"]Douglas County
[/TD]
[TD="class: xl73, width: 84, bgcolor: transparent"]61
[/TD]
[TD="class: xl70, width: 78, bgcolor: transparent, align: right"]104
[/TD]
[TD="class: xl70, width: 64, bgcolor: transparent, align: right"]104
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]104
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]104
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]104
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]104
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]104
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]104
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]104
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]104
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]104
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]104
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421"]Durango, La Plata Co
[/TD]
[TD="class: xl73, width: 84"]61
[/TD]
[TD="class: xl70, width: 78, align: right"]97
[/TD]
[TD="class: xl70, width: 64, align: right"]97
[/TD]
[TD="class: xl69, align: right"]97
[/TD]
[TD="class: xl69, align: right"]97
[/TD]
[TD="class: xl69, align: right"]97
[/TD]
[TD="class: xl69, align: right"]97
[/TD]
[TD="class: xl69, align: right"]97
[/TD]
[TD="class: xl69, align: right"]97
[/TD]
[TD="class: xl69, align: right"]138
[/TD]
[TD="class: xl69, align: right"]138
[/TD]
[TD="class: xl69, align: right"]138
[/TD]
[TD="class: xl69, align: right"]138
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421, bgcolor: transparent"]Fort Collins / Loveland, Larimer Co
[/TD]
[TD="class: xl73, width: 84, bgcolor: transparent"]56
[/TD]
[TD="class: xl70, width: 78, bgcolor: transparent, align: right"]91
[/TD]
[TD="class: xl70, width: 64, bgcolor: transparent, align: right"]91
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]91
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]91
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]91
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]91
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]91
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]91
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]91
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]91
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]91
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]91
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421"]Glenwood Springs / Grand Junction, Garfield Co, Mesa Co
[/TD]
[TD="class: xl73, width: 84"]51
[/TD]
[TD="class: xl70, width: 78, align: right"]84
[/TD]
[TD="class: xl70, width: 64, align: right"]84
[/TD]
[TD="class: xl69, align: right"]84
[/TD]
[TD="class: xl69, align: right"]84
[/TD]
[TD="class: xl69, align: right"]84
[/TD]
[TD="class: xl69, align: right"]84
[/TD]
[TD="class: xl69, align: right"]84
[/TD]
[TD="class: xl69, align: right"]84
[/TD]
[TD="class: xl69, align: right"]84
[/TD]
[TD="class: xl69, align: right"]84
[/TD]
[TD="class: xl69, align: right"]84
[/TD]
[TD="class: xl69, align: right"]84
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421, bgcolor: transparent"]Montrose, Montrose Co
[/TD]
[TD="class: xl73, width: 84, bgcolor: transparent"]56
[/TD]
[TD="class: xl70, width: 78, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl70, width: 64, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]92
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]92
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]92
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]92
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421"]Silverthorne / Breckenridge, Summit Co
[/TD]
[TD="class: xl73, width: 84"]56
[/TD]
[TD="class: xl70, width: 78, align: right"]92
[/TD]
[TD="class: xl70, width: 64, align: right"]92
[/TD]
[TD="class: xl69, align: right"]132
[/TD]
[TD="class: xl69, align: right"]132
[/TD]
[TD="class: xl69, align: right"]132
[/TD]
[TD="class: xl69, align: right"]132
[/TD]
[TD="class: xl69, align: right"]86
[/TD]
[TD="class: xl69, align: right"]86
[/TD]
[TD="class: xl69, align: right"]92
[/TD]
[TD="class: xl69, align: right"]92
[/TD]
[TD="class: xl69, align: right"]92
[/TD]
[TD="class: xl69, align: right"]92
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421, bgcolor: transparent"]Steamboat Springs, Routt Co
[/TD]
[TD="class: xl73, width: 84, bgcolor: transparent"]56
[/TD]
[TD="class: xl70, width: 78, bgcolor: transparent, align: right"]99
[/TD]
[TD="class: xl70, width: 64, bgcolor: transparent, align: right"]99
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]158
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]158
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]158
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]158
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]99
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]99
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]99
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]99
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]99
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]99
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421"]Telluride, San Miguel Co
[/TD]
[TD="class: xl73, width: 84"]71
[/TD]
[TD="class: xl70, width: 78, align: right"]107
[/TD]
[TD="class: xl70, width: 64, align: right"]107
[/TD]
[TD="class: xl69, align: right"]222
[/TD]
[TD="class: xl69, align: right"]222
[/TD]
[TD="class: xl69, align: right"]222
[/TD]
[TD="class: xl69, align: right"]222
[/TD]
[TD="class: xl69, align: right"]129
[/TD]
[TD="class: xl69, align: right"]129
[/TD]
[TD="class: xl69, align: right"]221
[/TD]
[TD="class: xl69, align: right"]221
[/TD]
[TD="class: xl69, align: right"]221
[/TD]
[TD="class: xl69, align: right"]221
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421, bgcolor: transparent"]Vail, Eagle Co
[/TD]
[TD="class: xl73, width: 84, bgcolor: transparent"]71
[/TD]
[TD="class: xl70, width: 78, bgcolor: transparent, align: right"]114
[/TD]
[TD="class: xl70, width: 64, bgcolor: transparent, align: right"]114
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]297
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]297
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]297
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]297
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]142
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]142
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]142
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]142
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]142
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]114
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421"]UTAH Standard Rate
[/TD]
[TD="class: xl73, width: 84"]46
[/TD]
[TD="class: xl70, width: 78, align: right"]83
[/TD]
[TD="class: xl70, width: 64, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[TD="class: xl69, align: right"]83
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421, bgcolor: transparent"]Moab, Grand Co
[/TD]
[TD="class: xl73, width: 84, bgcolor: transparent"]56
[/TD]
[TD="class: xl70, width: 78, bgcolor: transparent, align: right"]128
[/TD]
[TD="class: xl70, width: 64, bgcolor: transparent, align: right"]114
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]114
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]114
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]114
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]128
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]128
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]128
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]128
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]128
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]128
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]128
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421"]Park City, Summit Co
[/TD]
[TD="class: xl73, width: 84"]71
[/TD]
[TD="class: xl70, width: 78, align: right"]99
[/TD]
[TD="class: xl70, width: 64, align: right"]99
[/TD]
[TD="class: xl69, align: right"]211
[/TD]
[TD="class: xl69, align: right"]211
[/TD]
[TD="class: xl69, align: right"]211
[/TD]
[TD="class: xl69, align: right"]211
[/TD]
[TD="class: xl69, align: right"]99
[/TD]
[TD="class: xl69, align: right"]99
[/TD]
[TD="class: xl69, align: right"]99
[/TD]
[TD="class: xl69, align: right"]99
[/TD]
[TD="class: xl69, align: right"]99
[/TD]
[TD="class: xl69, align: right"]99
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421, bgcolor: transparent"]Provo, Utah Co
[/TD]
[TD="class: xl73, width: 84, bgcolor: transparent"]51
[/TD]
[TD="class: xl70, width: 78, bgcolor: transparent, align: right"]85
[/TD]
[TD="class: xl70, width: 64, bgcolor: transparent, align: right"]85
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]85
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]85
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]85
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]85
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]85
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]85
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]85
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]85
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]85
[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]85
[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 421"]Salt Lake City, Salt Lake Co, Tooele Co
[/TD]
[TD="class: xl73, width: 84"]61
[/TD]
[TD="class: xl70, width: 78, align: right"]103
[/TD]
[TD="class: xl70, width: 64, align: right"]103
[/TD]
[TD="class: xl69, align: right"]103
[/TD]
[TD="class: xl69, align: right"]115
[/TD]
[TD="class: xl69, align: right"]115
[/TD]
[TD="class: xl69, align: right"]115
[/TD]
[TD="class: xl69, align: right"]103
[/TD]
[TD="class: xl69, align: right"]103
[/TD]
[TD="class: xl69, align: right"]103
[/TD]
[TD="class: xl69, align: right"]103
[/TD]
[TD="class: xl69, align: right"]103
[/TD]
[TD="class: xl69, align: right"]103
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Here is the query form for the Per Diem Report: Very simple form, the drop down menu, once activated, will have the list of locations from the last post. Once chosen, the next cell should populate with the correct rate for the correct location & month of the travel date. If needed I could simplify the date query by having the employee enter the date as a 3 column entry (day, month, year) It seems so simple but my knowledge of Excel stops at the College Intermediate Level, so far... Thanks for your time and effort. What a great site!!![TABLE="width: 840"]
<colgroup><col width="64" style="width: 48pt;"> <col width="136" style="width: 102pt; mso-width-source: userset; mso-width-alt: 4973;"> <col width="125" style="width: 94pt; mso-width-source: userset; mso-width-alt: 4571;"> <col width="117" style="width: 88pt; mso-width-source: userset; mso-width-alt: 4278;"> <col width="92" style="width: 69pt; mso-width-source: userset; mso-width-alt: 3364;"> <col width="350" style="width: 263pt; mso-width-source: userset; mso-width-alt: 12800;"> <col width="116" style="width: 87pt; mso-width-source: userset; mso-width-alt: 4242;"> <col width="119" style="width: 89pt; mso-width-source: userset; mso-width-alt: 4352;"> <tbody>[TR]
[TD="class: xl80, width: 64, bgcolor: transparent"]HOTEL[/TD]
[TD="class: xl75, width: 136"]Client[/TD]
[TD="class: xl75, width: 125"]Project[/TD]
[TD="class: xl75, width: 117"]Job[/TD]
[TD="class: xl76, width: 92"]Travel Date[/TD]
[TD="class: xl75, width: 350"]Location[/TD]
[TD="class: xl77, width: 116"] Rate [/TD]
[TD="class: xl77, width: 119"] Total [/TD]
[/TR]
[TR]
[TD="class: xl68"]SBA[/TD]
[TD="class: xl68"]White Pass[/TD]
[TD="class: xl68"]Miner[/TD]
[TD="class: xl69"]11/26/2014[/TD]
[TD="class: xl68"]Choose from drop down menu[/TD]
[TD="class: xl70"] [/TD]
[TD="class: xl70"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl72, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl72"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl72, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl72"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl72, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl72"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl72, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl72"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl72, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl72"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl72, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl72"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl72, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl72"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl72, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl72"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl72, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl72"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl72, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl72"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl72, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl72"] [/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"]Total[/TD]
[TD="class: xl74, bgcolor: transparent"] [/TD]
[TD="class: xl74, bgcolor: transparent"] [/TD]
[TD="class: xl74, bgcolor: transparent"] [/TD]
[TD="class: xl74, bgcolor: transparent"] [/TD]
[TD="class: xl74, bgcolor: transparent"] [/TD]
[TD="class: xl79, bgcolor: transparent"] $ - [/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl79, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Maybe this:

Layout

[TABLE="width: 566"]
<tbody>[TR]
[TD="class: xl65, width: 134, bgcolor: transparent"]Locations
[/TD]
[TD="class: xl66, width: 52, bgcolor: transparent"]Per Diem
[/TD]
[TD="class: xl66, width: 47, bgcolor: transparent"]Lodging
[/TD]
[TD="class: xl65, width: 42, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 42, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 42, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 42, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 42, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 42, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 42, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 42, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 42, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 42, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 42, bgcolor: transparent"][/TD]
[TD="class: xl69, width: 52, bgcolor: transparent"]Data Base
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"]Meals
[/TD]
[TD="class: xl67, bgcolor: transparent"]Out
[/TD]
[TD="class: xl67, bgcolor: transparent"]Nov
[/TD]
[TD="class: xl67, bgcolor: transparent"]Dez
[/TD]
[TD="class: xl67, bgcolor: transparent"]Jan
[/TD]
[TD="class: xl67, bgcolor: transparent"]Fev
[/TD]
[TD="class: xl67, bgcolor: transparent"]Mar
[/TD]
[TD="class: xl67, bgcolor: transparent"]Abr
[/TD]
[TD="class: xl67, bgcolor: transparent"]Mai
[/TD]
[TD="class: xl67, bgcolor: transparent"]Jun
[/TD]
[TD="class: xl67, bgcolor: transparent"]Jul
[/TD]
[TD="class: xl67, bgcolor: transparent"]Ago
[/TD]
[TD="class: xl67, bgcolor: transparent"]Set
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]OREGON Standard Rate
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]46
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]86
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]92
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]94
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]98
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]85
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]80
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]99
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]89
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]91
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]93
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]97
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Beaverton, Washington Co
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]51
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]89
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]89
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]91
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]85
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]91
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]96
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]80
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]84
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]95
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]93
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]82
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Bend, Deschutes Co
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]61
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]98
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]98
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]93
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]82
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]87
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]91
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]94
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]92
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]80
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]84
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]82
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]98
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Clackamas, Clackamas Co
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]61
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]90
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]91
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]81
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]95
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]81
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]87
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]83
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]90
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]87
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]94
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]92
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]88
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]*************************
[/TD]
[TD="class: xl68, bgcolor: transparent"]*********
[/TD]
[TD="class: xl68, bgcolor: transparent"]********
[/TD]
[TD="class: xl68, bgcolor: transparent"]*******
[/TD]
[TD="class: xl68, bgcolor: transparent"]*******
[/TD]
[TD="class: xl68, bgcolor: transparent"]*******
[/TD]
[TD="class: xl68, bgcolor: transparent"]*******
[/TD]
[TD="class: xl68, bgcolor: transparent"]*******
[/TD]
[TD="class: xl68, bgcolor: transparent"]*******
[/TD]
[TD="class: xl68, bgcolor: transparent"]*******
[/TD]
[TD="class: xl68, bgcolor: transparent"]*******
[/TD]
[TD="class: xl68, bgcolor: transparent"]*******
[/TD]
[TD="class: xl68, bgcolor: transparent"]*******
[/TD]
[TD="class: xl68, bgcolor: transparent"]*******
[/TD]
[TD="class: xl65, bgcolor: transparent"]*********
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 474"]
<tbody>[TR]
[TD="class: xl66, width: 47, bgcolor: transparent"]Hotel
[/TD]
[TD="class: xl66, width: 62, bgcolor: transparent"]Client
[/TD]
[TD="class: xl66, width: 41, bgcolor: transparent"]Project
[/TD]
[TD="class: xl66, width: 67, bgcolor: transparent"]Job
[/TD]
[TD="class: xl66, width: 67, bgcolor: transparent"]Travel Date
[/TD]
[TD="class: xl66, width: 134, bgcolor: transparent"]Location
[/TD]
[TD="class: xl66, width: 67, bgcolor: transparent"]Meals Rate
[/TD]
[TD="class: xl66, width: 67, bgcolor: transparent"]Lodging Rate
[/TD]
[TD="class: xl66, width: 37, bgcolor: transparent"]Total
[/TD]
[TD="class: xl68, width: 42, bgcolor: transparent"]Main
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]SBA
[/TD]
[TD="class: xl65, bgcolor: transparent"]White Pass
[/TD]
[TD="class: xl65, bgcolor: transparent"]Miner
[/TD]
[TD="class: xl67, bgcolor: transparent"]Job01
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]26/11/2014
[/TD]
[TD="class: xl65, bgcolor: transparent"]OREGON Standard Rate
[/TD]
[TD="class: xl69, bgcolor: yellow, align: right"]46
[/TD]
[TD="class: xl69, bgcolor: yellow, align: right"]92
[/TD]
[TD="class: xl69, bgcolor: yellow, align: right"]138
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]SBA
[/TD]
[TD="class: xl65, bgcolor: transparent"]White Pass
[/TD]
[TD="class: xl65, bgcolor: transparent"]Miner
[/TD]
[TD="class: xl67, bgcolor: transparent"]Job02
[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]27/01/2014
[/TD]
[TD="class: xl66, bgcolor: transparent"]Clackamas, Clackamas Co
[/TD]
[TD="class: xl69, bgcolor: yellow, align: right"]61
[/TD]
[TD="class: xl69, bgcolor: yellow, align: right"]95
[/TD]
[TD="class: xl69, bgcolor: yellow, align: right"]156
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]********
[/TD]
[TD="class: xl65, bgcolor: transparent"]***********
[/TD]
[TD="class: xl65, bgcolor: transparent"]******
[/TD]
[TD="class: xl65, bgcolor: transparent"]************
[/TD]
[TD="class: xl65, bgcolor: transparent"]************
[/TD]
[TD="class: xl65, bgcolor: transparent"]*************************
[/TD]
[TD="class: xl65, bgcolor: transparent"]************
[/TD]
[TD="class: xl65, bgcolor: transparent"]************
[/TD]
[TD="class: xl65, bgcolor: transparent"]******
[/TD]
[TD="class: xl65, bgcolor: transparent"]*******
[/TD]
[/TR]
</tbody>[/TABLE]

Formulas

Code: 
In G2

=VLOOKUP($F2,'Data Base'!$A$3:$B$59,2,0)

In H2

=VLOOKUP($F2,'Data Base'!$A$3:$N$59,MATCH(TEXT($E2,"mmm"),'Data Base'!$A$2:$N$2,0),0)

In I2

=SUM($G2:$H2)

Markmzz
 
Upvote 0
It works!!! I adapted your formula to my environment and after a few tries it works perfectly. So, the "mmm" transformed a short date into a month?. Thank you so very much Mark.
 
Upvote 0
Bear Books said:
It works!!! I adapted your formula to my environment and after a few tries it works perfectly. So, the "mmm" transformed a short date into a month?. Thank you so very much Mark.
Click to expand...

You're welcome and thanks for the feedback.

Markmzz
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
DATE Formula - Find earliest posted date in range of dates given month and year
Replies
2
Views
1K
Derick63
D
D
  • Question Question
Multiple Criteria for results with dates and numbers
Replies
0
Views
422
dwinsburg
D
E
  • Question Question
Pay Scale Analysis. RANKINGS, XLOOKUPS, IFS or all of the above nested!?? Please Help!
Replies
0
Views
532
Emaus_Sedan
E
T
  • Solved
Data selection -- Final Outcome and Automatic fill in of specific Cells based on Data selected
Replies
8
Views
1K
maabadi
M
MrDB4Excel
  • Solved
If Exist in a Column Range
Replies
8
Views
2K
DanteAmor
DanteAmor

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top