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.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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>
locationmeals rate123456789101112
Montgomery24303234363840424446485052
Juneau44252729313335373941434547
Phoenix46252729313335373941434547
Little Rock30252729313335373941434547
Sacramento42202224262830323436384042
Denver24303234363840424446485052
Hartford22303234363840424346485052
Dover42303234363840424446485052
Tallahassee37252729313335373941434547
Atlanta43303234363840424446485052
Honolulu40404244464850525456586062
Boise40252729313335373941434547
Springfield34303234363840424446485052
Indianapolis33404244464850525456586062
Des Moines43303234363840424446485052
Topeka27353739414345474951535557
Frankfort50303234363840424446485052
Baton Rouge34202224262831323436384042

<tbody>
</tbody>


<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>
Rate
Location:Hartford22
Month:538

<tbody>
</tbody>

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>
location
meals rate
1
2
3
4
5
6
7
8
9
10
11
12
Montgomery
24
30
32
34
36
38
40
42
44
46
48
50
52
Juneau
44
25
27
29
31
33
35
37
39
41
43
45
47
Phoenix
46
25
27
29
31
33
35
37
39
41
43
45
47
Little Rock
30
25
27
29
31
33
35
37
39
41
43
45
47
Sacramento
42
20
22
24
26
28
30
32
34
36
38
40
42
Denver
24
30
32
34
36
38
40
42
44
46
48
50
52
Hartford
22
30
32
34
36
38
40
42
43
46
48
50
52
Dover
42
30
32
34
36
38
40
42
44
46
48
50
52
Tallahassee
37
25
27
29
31
33
35
37
39
41
43
45
47
Atlanta
43
30
32
34
36
38
40
42
44
46
48
50
52
Honolulu
40
40
42
44
46
48
50
52
54
56
58
60
62
Boise
40
25
27
29
31
33
35
37
39
41
43
45
47
Springfield
34
30
32
34
36
38
40
42
44
46
48
50
52
Indianapolis
33
40
42
44
46
48
50
52
54
56
58
60
62
Des Moines
43
30
32
34
36
38
40
42
44
46
48
50
52
Topeka
27
35
37
39
41
43
45
47
49
51
53
55
57
Frankfort
50
30
32
34
36
38
40
42
44
46
48
50
52
Baton Rouge
34
20
22
24
26
28
31
32
34
36
38
40
42

<tbody>
</tbody>


<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>
Rate
Location:
Hartford
22
Month:
5
38

<tbody>
</tbody>

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?
Locations
Per Diem
Meals
Lodging: Oct
 Nov
 Dec
 Jan
 Feb
 Mar
 Apr
 May
 Jun
 Jul
 Aug
 Sep
OREGON Standard Rate
46
83
83
83
83
83
83
83
83
83
83
83
83
Beaverton, Washington Co
51
106
106
106
106
106
106
106
106
106
106
106
106
Bend, Deschutes Co
61
89
89
89
89
89
89
89
89
89
107
107
89
Clackamas, Clackamas Co
61
90
90
90
90
90
90
90
90
90
90
90
90
Eugene / Florence, Lane Co
51
94
94
94
94
94
94
94
94
94
94
94
94
Lincoln City, Lincoln Co
56
94
94
94
94
94
94
94
94
94
121
121
94
Portland, Multnomah Co
66
126
126
126
126
126
126
126
126
126
126
126
126
Seaside, Clatsop Co
51
96
96
96
96
96
96
96
96
96
138
138
96
WASHINGTON Standard Rate
46
83
83
83
83
83
83
83
83
83
83
83
83
Anacortes / Coupeville / Oak Harbor, Skagit, Island, San Juan
61
91
91
91
91
91
91
91
91
91
91
91
91
Everett / Lynnwood, Snohomish Co
61
104
104
104
104
104
104
104
104
104
104
104
104
Ocean Shores, Grays Harbor Co
51
83
83
83
83
83
83
83
83
83
105
105
83
Olympia / Tumwater, Thurston Co
61
94
94
94
94
94
94
94
94
94
94
94
94
Port Angeles / Port Townsend, Clallam Co, Jefferson Co
61
95
95
95
95
95
95
95
95
95
122
122
95
Richland / Pasco, Benton Co, Franklin Co
46
90
90
90
90
90
90
90
90
90
90
90
90
Seattle, King Co
71
152
152
152
152
152
152
152
152
152
152
152
152
Spokane, Spokane Co
61
88
88
88
88
88
88
88
88
88
88
88
88
Tacoma, Pierce, Co
61
106
106
106
106
106
106
106
106
106
106
106
106
Vancouver, Clark Co, Cowlitz Co, Skamania Co
56
126
126
126
126
126
126
126
126
126
126
126
126
IDAHO Standard Rate
46
83
83
83
83
83
83
83
83
83
83
83
83
Bonner's Ferry / Sandpoint, Bonner Co, Boundary Co, Shoshone Co
61
83
83
83
83
83
83
83
83
83
107
107
83
Coeur d'Alene, Kootenai Co
61
85
85
85
85
85
85
85
85
126
126
126
85
Driggs / Idaho Falls, Bonneville Co, Fremont Co, Teton Co
46
84
84
84
84
84
84
84
84
84
84
84
84
Sun Valley / Ketchum, Blaine Co
71
93
93
93
93
93
93
93
93
93
116
116
93
MONTANA Standard Rate
46
83
83
83
83
83
83
83
83
83
83
83
83
Big Sky / West Yellowstone, Gallatin Co
61
83
83
83
83
83
83
83
83
117
117
117
117
Butte, Silber Bow
51
88
88
88
88
88
88
88
88
88
88
88
88
Glendive / Sidney, Dawson Co, Richland Co
56
105
105
105
105
105
105
105
105
105
105
105
105
Helena, Lewis & Clark Co
56
88
88
88
88
88
88
88
88
88
88
88
88
Missoula / Polson / Kalispell, Missoula Co, Lake Co, Flathead Co
51
89
89
89
89
89
89
89
89
89
114
114
89
WYOMING Standard Rate
46
83
83
83
83
83
83
83
83
83
83
83
83
Cody, Park Co
51
93
93
93
93
93
93
93
93
132
132
132
132
Evanston / Rock Springs, Sweetwater Co, Uinta Co
51
89
89
89
89
89
89
89
89
89
89
89
89
Gillette, Campbell Co
51
83
83
83
83
83
83
83
83
88
88
88
83
Jackson / Pinedale, Teton Co, Sublette Co
56
117
117
117
117
117
117
117
117
117
180
180
117
Sheridan, Sheridan Co
56
83
83
83
83
83
83
83
83
88
88
88
83
COLORADO Standard Rate
46
83
83
83
83
83
83
83
83
83
83
83
83
Aspen, Pitkin Co
71
122
122
247
247
247
247
120
120
213
213
213
122
Boulder / Broomfield, Boulder Co, Broomfield Co
61
111
111
111
111
111
111
111
111
111
111
111
111
Colorado Springs, El Paso Co
66
87
87
87
87
87
87
87
87
87
87
87
87
Cortez, Montezuma Co
51
87
87
87
87
87
87
87
87
109
109
109
109
Crested Butte / Gunnison, Gunnison Co
51
83
83
83
83
83
83
83
83
94
94
94
83
Denver / Aurora, Denver Co, Adams Co, Arapahoe Co, Jefferson Co
66
156
156
156
156
156
156
156
156
156
156
156
156
Douglas County
61
104
104
104
104
104
104
104
104
104
104
104
104
Durango, La Plata Co
61
97
97
97
97
97
97
97
97
138
138
138
138
Fort Collins / Loveland, Larimer Co
56
91
91
91
91
91
91
91
91
91
91
91
91
Glenwood Springs / Grand Junction, Garfield Co, Mesa Co
51
84
84
84
84
84
84
84
84
84
84
84
84
Montrose, Montrose Co
56
83
83
83
83
83
83
83
83
92
92
92
92
Silverthorne / Breckenridge, Summit Co
56
92
92
132
132
132
132
86
86
92
92
92
92
Steamboat Springs, Routt Co
56
99
99
158
158
158
158
99
99
99
99
99
99
Telluride, San Miguel Co
71
107
107
222
222
222
222
129
129
221
221
221
221
Vail, Eagle Co
71
114
114
297
297
297
297
142
142
142
142
142
114
UTAH Standard Rate
46
83
83
83
83
83
83
83
83
83
83
83
83
Moab, Grand Co
56
128
114
114
114
114
128
128
128
128
128
128
128
Park City, Summit Co
71
99
99
211
211
211
211
99
99
99
99
99
99
Provo, Utah Co
51
85
85
85
85
85
85
85
85
85
85
85
85
Salt Lake City, Salt Lake Co, Tooele Co
61
103
103
103
115
115
115
103
103
103
103
103
103

<tbody>
</tbody>
 
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!!!
HOTELClientProjectJobTravel DateLocation Rate Total
SBAWhite PassMiner11/26/2014Choose from drop down menu
Total $ -

<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>
</tbody>
 
Upvote 0
Maybe this:

Layout

Locations
Per Diem
Lodging
Data Base
Meals
Out
Nov
Dez
Jan
Fev
Mar
Abr
Mai
Jun
Jul
Ago
Set
OREGON Standard Rate
46
86
92
94
98
85
80
99
83
89
91
93
97
Beaverton, Washington Co
51
89
89
91
85
91
96
83
80
84
95
93
82
Bend, Deschutes Co
61
98
98
93
82
87
91
94
92
80
84
82
98
Clackamas, Clackamas Co
61
90
91
81
95
81
87
83
90
87
94
92
88
*************************
*********
********
*******
*******
*******
*******
*******
*******
*******
*******
*******
*******
*******
*********

<tbody>
</tbody>

Hotel
Client
Project
Job
Travel Date
Location
Meals Rate
Lodging Rate
Total
Main
SBA
White Pass
Miner
Job01
26/11/2014
OREGON Standard Rate
46
92
138
SBA
White Pass
Miner
Job02
27/01/2014
Clackamas, Clackamas Co
61
95
156
********
***********
******
************
************
*************************
************
************
******
*******

<tbody>
</tbody>

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
885
Derick63
D
D
  • Question
Multiple Criteria for results with dates and numbers
Replies
0
Views
341
dwinsburg
D
E
  • Question
Pay Scale Analysis. RANKINGS, XLOOKUPS, IFS or all of the above nested!?? Please Help!
Replies
0
Views
400
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
922
DanteAmor
DanteAmor

Forum statistics

Threads
1,214,927
Messages
6,122,311
Members
449,080
Latest member
jmsotelo

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