Look Up for Previous and Current in Table

[thbutterflycollector_]

Hi,

I have a summary table which summarises data from a given month. This is updated month on month as cost data is actualised.

On the left handside I want to include a formula which searches for the current and most recent "previous" period from the top row i.e. looking in the table to the right and finding the further occurence of "previous" on the right handside.

Can anyone help? Thanks

Previous

Previous

Previous

Previous

Previous

Previous

Previous

Previous

Current

Current

Previous

Month 1

Month 2

Month 3

Month 4

Month 5

Month 6

Month 7

Month 8

Month 9

Month 10

Month 11

Period X

52

67

Period X

10​

25​

25​

34​

25​

26​

78​

67​

52​

Cumulative X

342

290

Cumulative X

10​

35​

60​

94​

119​

145​

223​

290​

342​

Period Y

4

3

Period Y

1​

2​

2​

3​

2​

4​

2​

3​

4​

Cumulative Y

23

19

Cumulative Y

1​

3​

5​

8​

10​

14​

16​

19​

23​

Period Z

0.01

0.01

Period Z

0.01​

0.01​

0.01​

0.01​

0.01​

0.01​

0.01​

0.01​

0.01​

Cumulative Z

0.09

0.08

Cumulative Z

0.01​

0.02​

0.03​

0.04​

0.05​

0.06​

0.07​

0.08​

0.09​

 

[dreid1011]

Couple of XLOOKUPS seem to do the trick:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1						Previous	Previous	Previous	Previous	Previous	Previous	Previous	Previous	Current
2		Current	Previous			Month 1	Month 2	Month 3	Month 4	Month 5	Month 6	Month 7	Month 8	Month 9	Month 10	Month 11
3	Period X	52	67		Period X	10	25	25	34	25	26	78	67	52
4	Cumulative X	342	290		Cumulative X	10	35	60	94	119	145	223	290	342
5	Period Y	4	3		Period Y	1	2	2	3	2	4	2	3	4
6	Cumulative Y	23	19		Cumulative Y	1	3	5	8	10	14	16	19	23
7	Period Z	0.01	0.01		Period Z	0.01	0.01	0.01	0.01	0.01	0.01	0.01	0.01	0.01
8	Cumulative Z	0.09	0.08		Cumulative Z	0.01	0.02	0.03	0.04	0.05	0.06	0.07	0.08	0.09
Sheet1

Cell Formulas
Range	Formula
B3:B8	B3	=XLOOKUP($B$2,$F$1:$P$1,F3:P3)
C3:C8	C3	=XLOOKUP($C$2,$F$1:$P$1,F3:P3,,,-1)

 

[dreid1011]

Or a spilled version:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1						Previous	Previous	Previous	Previous	Previous	Previous	Previous	Previous	Current
2		Current	Previous			Month 1	Month 2	Month 3	Month 4	Month 5	Month 6	Month 7	Month 8	Month 9	Month 10	Month 11
3	Period X	52	67		Period X	10	25	25	34	25	26	78	67	52
4	Cumulative X	342	290		Cumulative X	10	35	60	94	119	145	223	290	342
5	Period Y	4	3		Period Y	1	2	2	3	2	4	2	3	4
6	Cumulative Y	23	19		Cumulative Y	1	3	5	8	10	14	16	19	23
7	Period Z	0.01	0.01		Period Z	0.01	0.01	0.01	0.01	0.01	0.01	0.01	0.01	0.01
8	Cumulative Z	0.09	0.08		Cumulative Z	0.01	0.02	0.03	0.04	0.05	0.06	0.07	0.08	0.09
Sheet1

Cell Formulas
Range	Formula
B3:C8	B3	=CHOOSECOLS(TAKE(FILTER(F3:P8,F1:P1<>""),,-2),2,1)
Dynamic array formulas.

 

[thbutterflycollector_]

Or a spilled version:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1						Previous	Previous	Previous	Previous	Previous	Previous	Previous	Previous	Current
2		Current	Previous			Month 1	Month 2	Month 3	Month 4	Month 5	Month 6	Month 7	Month 8	Month 9	Month 10	Month 11
3	Period X	52	67		Period X	10	25	25	34	25	26	78	67	52
4	Cumulative X	342	290		Cumulative X	10	35	60	94	119	145	223	290	342
5	Period Y	4	3		Period Y	1	2	2	3	2	4	2	3	4
6	Cumulative Y	23	19		Cumulative Y	1	3	5	8	10	14	16	19	23
7	Period Z	0.01	0.01		Period Z	0.01	0.01	0.01	0.01	0.01	0.01	0.01	0.01	0.01
8	Cumulative Z	0.09	0.08		Cumulative Z	0.01	0.02	0.03	0.04	0.05	0.06	0.07	0.08	0.09
Sheet1

Cell Formulas
Range	Formula
B3:C8	B3	=CHOOSECOLS(TAKE(FILTER(F3:P8,F1:P1<>""),,-2),2,1)
Dynamic array formulas.

Thank you - I have gone with the XLOOKUPS.

 

[dreid1011]

Thank you - I have gone with the XLOOKUPS.

Great! You're welcome, and happy to help.