Match/Lookup separate tables into one column

Ashleytaylor1702

New Member
Joined
Jul 9, 2018
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hi All,

I am in the process of creating a budget planner and have two separate ingoing/outgoing tables in sheet 1.

The first table has all expenses that are debited/credited on a set day of the month (For example 1st,4th,18th)

The second table has all expenses that are debited/credited on a set day of the week (For example Monday, or Wednesday)

What I require is a formula in sheet 2 in columns B & C that lookups up the expenses and amounts from sheet 1 and transfers them across to the relevant day of the month or day of the week
and repeats the pattern through the months in sheet 2.

Please see attached example workbook - Note that I have manually pasted my requirement and changed the font colour to red for clarity.

Many Thanks

Sheet 1

Test Planner V1.xlsx
ABCDEFG
1Date of monthIngoing/OutgoingAmountDay of weekIngoing/OutgoingAmount
21Income 1 -100FridayFood Shopping10
31Mortgage25
41
51
61
71
81
91
102Gas15
112
122
132
142
152
162
172
183Electric10
193Internet12
203House Insurance8
213
223
233
243
253
264
274
284
294
304
314
324
334
345Car Insurance4
355
365
375
385
395
405
415
426
436
446
456
466
476
486
496
507
517
527
537
547
557
567
577
588
598
608
618
628
638
648
658
669
679
689
699
709
719
729
739
7410
7510
7610
7710
7810
7910
8010
8110
8211
8311
8411
8511
8611
8711
8811
8911
9012
9112
9212
9312
9412
9512
9612
9712
9813
9913
10013
10113
10213
10313
10413
10513
10614
10714
10814
10914
11014
11114
11214
11314
11415Income 2-125
11515
11615
11715
11815
11915
12015
12115
12216
12316
12416
12516
12616
12716
12816
12916
13017
13117
13217
13317
13417
13517
13617
13717
13818Car Payment10
13918
14018
14118
14218
14318
14418
14518
14619
14719
14819
14919
15019
15119
15219
15319
15420
15520
15620
15720
15820
15920
16020
16120
16221
16321
16421
16521
16621
16721
16821
16921
17022Shopping10
17122
17222
17322
17422
17522
17622
17722
17823
17923
18023
18123
18223
18323
18423
18523
18624
18724
18824
18924
19024
19124
19224
19324
19425
19525
19625
19725
19825
19925
20025
20125
20226
20326
20426
20526
20626
20726
20826
20926
21027
21127
21227
21327
21427
21527
21627
21727
21828Water6
21928
22028
22128
22228
22328
22428
22528
Sheet1


Sheet 2
Test Planner V1.xlsx
ABCD
1DateIngoing/OutgoingAmountBalance
2100
3Saturday 01/04/2023Income 1 -100200
4Saturday 01/04/2023Mortgage25175
5Saturday 01/04/2023175
6Saturday 01/04/2023175
7Saturday 01/04/2023175
8Saturday 01/04/2023175
9Saturday 01/04/2023175
10Saturday 01/04/2023175
11Sunday 02/04/2023Gas15160
12Sunday 02/04/2023160
13Sunday 02/04/2023160
14Sunday 02/04/2023160
15Sunday 02/04/2023160
16Sunday 02/04/2023160
17Sunday 02/04/2023160
18Sunday 02/04/2023160
19Monday 03/04/2023Electric10150
20Monday 03/04/2023Internet12138
21Monday 03/04/2023House Insurance8130
22Monday 03/04/2023130
23Monday 03/04/2023130
24Monday 03/04/2023130
25Monday 03/04/2023130
26Monday 03/04/2023130
27Tuesday 04/04/2023130
28Tuesday 04/04/2023130
29Tuesday 04/04/2023130
30Tuesday 04/04/2023130
31Tuesday 04/04/2023130
32Tuesday 04/04/2023130
33Tuesday 04/04/2023130
34Tuesday 04/04/2023130
35Wednesday 05/04/2023Car Insurance4126
36Wednesday 05/04/2023126
37Wednesday 05/04/2023126
38Wednesday 05/04/2023126
39Wednesday 05/04/2023126
40Wednesday 05/04/2023126
41Wednesday 05/04/2023126
42Wednesday 05/04/2023126
43Thursday 06/04/2023126
44Thursday 06/04/2023126
45Thursday 06/04/2023126
46Thursday 06/04/2023126
47Thursday 06/04/2023126
48Thursday 06/04/2023126
49Thursday 06/04/2023126
50Thursday 06/04/2023126
51Friday 07/04/2023Food Shopping10116
52Friday 07/04/2023116
53Friday 07/04/2023116
54Friday 07/04/2023116
55Friday 07/04/2023116
56Friday 07/04/2023116
57Friday 07/04/2023116
58Friday 07/04/2023116
59Saturday 08/04/2023116
60Saturday 08/04/2023116
61Saturday 08/04/2023116
62Saturday 08/04/2023116
63Saturday 08/04/2023116
64Saturday 08/04/2023116
65Saturday 08/04/2023116
66Saturday 08/04/2023116
67Sunday 09/04/2023116
68Sunday 09/04/2023116
69Sunday 09/04/2023116
70Sunday 09/04/2023116
71Sunday 09/04/2023116
72Sunday 09/04/2023116
73Sunday 09/04/2023116
74Sunday 09/04/2023116
75Monday 10/04/2023116
76Monday 10/04/2023116
77Monday 10/04/2023116
78Monday 10/04/2023116
79Monday 10/04/2023116
80Monday 10/04/2023116
81Monday 10/04/2023116
82Monday 10/04/2023116
83Tuesday 11/04/2023116
84Tuesday 11/04/2023116
85Tuesday 11/04/2023116
86Tuesday 11/04/2023116
87Tuesday 11/04/2023116
88Tuesday 11/04/2023116
89Tuesday 11/04/2023116
90Tuesday 11/04/2023116
91Wednesday 12/04/2023116
92Wednesday 12/04/2023116
93Wednesday 12/04/2023116
94Wednesday 12/04/2023116
95Wednesday 12/04/2023116
96Wednesday 12/04/2023116
97Wednesday 12/04/2023116
98Wednesday 12/04/2023116
99Thursday 13/04/2023116
100Thursday 13/04/2023116
101Thursday 13/04/2023116
102Thursday 13/04/2023116
103Thursday 13/04/2023116
104Thursday 13/04/2023116
105Thursday 13/04/2023116
106Thursday 13/04/2023116
107Friday 14/04/2023Food Shopping10106
108Friday 14/04/2023106
109Friday 14/04/2023106
110Friday 14/04/2023106
111Friday 14/04/2023106
112Friday 14/04/2023106
113Friday 14/04/2023106
114Friday 14/04/2023106
115Saturday 15/04/2023Income 2-125231
116Saturday 15/04/2023231
117Saturday 15/04/2023231
118Saturday 15/04/2023231
119Saturday 15/04/2023231
120Saturday 15/04/2023231
121Saturday 15/04/2023231
122Saturday 15/04/2023231
123Sunday 16/04/2023231
124Sunday 16/04/2023231
125Sunday 16/04/2023231
126Sunday 16/04/2023231
127Sunday 16/04/2023231
128Sunday 16/04/2023231
129Sunday 16/04/2023231
130Sunday 16/04/2023231
131Monday 17/04/2023231
132Monday 17/04/2023231
133Monday 17/04/2023231
134Monday 17/04/2023231
135Monday 17/04/2023231
136Monday 17/04/2023231
137Monday 17/04/2023231
138Monday 17/04/2023231
139Tuesday 18/04/2023Car Payment10221
140Tuesday 18/04/2023221
141Tuesday 18/04/2023221
142Tuesday 18/04/2023221
143Tuesday 18/04/2023221
144Tuesday 18/04/2023221
145Tuesday 18/04/2023221
146Tuesday 18/04/2023221
147Wednesday 19/04/2023221
148Wednesday 19/04/2023221
149Wednesday 19/04/2023221
150Wednesday 19/04/2023221
151Wednesday 19/04/2023221
152Wednesday 19/04/2023221
153Wednesday 19/04/2023221
154Wednesday 19/04/2023221
155Thursday 20/04/2023221
156Thursday 20/04/2023221
157Thursday 20/04/2023221
158Thursday 20/04/2023221
159Thursday 20/04/2023221
160Thursday 20/04/2023221
161Thursday 20/04/2023221
162Thursday 20/04/2023221
163Friday 21/04/2023Food Shopping10211
164Friday 21/04/2023211
165Friday 21/04/2023211
166Friday 21/04/2023211
167Friday 21/04/2023211
168Friday 21/04/2023211
169Friday 21/04/2023211
170Friday 21/04/2023211
171Saturday 22/04/2023Shopping10201
172Saturday 22/04/2023201
173Saturday 22/04/2023201
174Saturday 22/04/2023201
175Saturday 22/04/2023201
176Saturday 22/04/2023201
177Saturday 22/04/2023201
178Saturday 22/04/2023201
179Sunday 23/04/2023201
180Sunday 23/04/2023201
181Sunday 23/04/2023201
182Sunday 23/04/2023201
183Sunday 23/04/2023201
184Sunday 23/04/2023201
185Sunday 23/04/2023201
186Sunday 23/04/2023201
187Monday 24/04/2023201
188Monday 24/04/2023201
189Monday 24/04/2023201
190Monday 24/04/2023201
191Monday 24/04/2023201
192Monday 24/04/2023201
193Monday 24/04/2023201
194Monday 24/04/2023201
195Tuesday 25/04/2023201
196Tuesday 25/04/2023201
197Tuesday 25/04/2023201
198Tuesday 25/04/2023201
199Tuesday 25/04/2023201
200Tuesday 25/04/2023201
201Tuesday 25/04/2023201
202Tuesday 25/04/2023201
203Wednesday 26/04/2023201
204Wednesday 26/04/2023201
205Wednesday 26/04/2023201
206Wednesday 26/04/2023201
207Wednesday 26/04/2023201
208Wednesday 26/04/2023201
209Wednesday 26/04/2023201
210Wednesday 26/04/2023201
211Thursday 27/04/2023201
212Thursday 27/04/2023201
213Thursday 27/04/2023201
214Thursday 27/04/2023201
215Thursday 27/04/2023201
216Thursday 27/04/2023201
217Thursday 27/04/2023201
218Thursday 27/04/2023201
219Friday 28/04/2023Water6195
220Friday 28/04/2023Food Shopping10185
221Friday 28/04/2023185
222Friday 28/04/2023185
223Friday 28/04/2023185
224Friday 28/04/2023185
225Friday 28/04/2023185
226Friday 28/04/2023185
227Saturday 29/04/2023185
228Saturday 29/04/2023185
229Saturday 29/04/2023185
230Saturday 29/04/2023185
231Saturday 29/04/2023185
232Saturday 29/04/2023185
233Saturday 29/04/2023185
234Saturday 29/04/2023185
235Sunday 30/04/2023185
236Sunday 30/04/2023185
237Sunday 30/04/2023185
238Sunday 30/04/2023185
239Sunday 30/04/2023185
240Sunday 30/04/2023185
241Sunday 30/04/2023185
242Sunday 30/04/2023185
243Monday 01/05/2023Income 1 -100285
244Monday 01/05/2023Mortgage25260
245Monday 01/05/2023260
246Monday 01/05/2023260
247Monday 01/05/2023260
248Monday 01/05/2023260
249Monday 01/05/2023260
250Monday 01/05/2023260
251Tuesday 02/05/2023Gas15245
252Tuesday 02/05/2023245
Sheet2
Cell Formulas
RangeFormula
D3:D252D3=D2-C3
 
@Ashleytaylor1702There is lots of information on Powery Query on You Tube etc. To increase the date range just add the dates into the Days of Week and Month tables. Then when you need to add costs, just fill in the cost column. Once done, go to Data Ribbon, under Queries and Connections choose Refresh All. The Combined Table will then populate.

Thanks, I will have a go at doing this. My original plan was to use 'general' format in the DOM table in the date column (ie 111,222,333). Then I was going to add another column next to the date column in the combined table which would have the same numbers (ie 111,222,333) and match on these columns.

This would save me from having to input all the data for several months in the DOM table.

Is this possible?

Thanks
 
Upvote 0

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
I have created a Power Query solution

@Skybluekid I have got what I need now by adapting your Power Query template. Thanks a lot.

Only issue I have is that the power query seems to be rounding my ingoings/outgoings column up or down?

Would you be able to advise how to show all decimal places? ( I have tried changing type to currency to no avail)

Thanks again
 
Upvote 0
Glad to help.
Two Options.

1 In Power Query, set the Column that has the costs, in the Combi Table to Decimal number (1.2) as a Step

2 Format the table on the Excel sheet to 0.00.

Hope this helps
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

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