Lookup the first and last column in a range

[reacon84]

Hi all, I'm looking to create a formula that can return the opening or closing balance, depending on the month input. I don't want it to be using the week at all.

For example, in the below image, I have weekly cash balances and I'd like to be able to input the month in the yellow box (ie month 2 in this example) and it returns the opening and closing balances.

Thanks in advance for any help!

Week	1	2	3	4	5	6	7	8
Month	1	1	1	1	2	2	2	2
Opening Balance	0	150	390	585	950	1240	1560	1715
Money In	200	300	250	400	330	360	200	180
Money Out	50	60	55	35	40	40	45	35
Closing Balance	150	390	585	950	1240	1560	1715	1860
Month:	2
Opening Balance	950
Closing Balance	1860

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	Week	1	2	3	4	5	6	7	8
2	Month	1	1	1	1	2	2	2	2
3
4	Opening Balance	0	150	390	585	950	1240	1560	1715
5	Money In	200	300	250	400	330	360	200	180
6	Money Out	50	60	55	35	40	40	45	35
7	Closing Balance	150	390	585	950	1240	1560	1715	1860
8
9	Month:	2
10	Opening Balance	950
11	Closing Balance	1860
Data

Cell Formulas
Range	Formula
B10	B10	=INDEX(B4:P4,MATCH(B9,B2:P2,0))
B11	B11	=LOOKUP(2,1/(B2:P2=B9),B7:P7)

 

[Peter_SSs]

See if these would work for you.

22 07 07.xlsm
	A	B	C	D	E	F	G	H	I
1	Week	1	2	3	4	5	6	7	8
2	Month	1	1	1	1	2	2	2	2
3
4	Opening Balance	0	150	390	585	950	1240	1560	1715
5	Money In	200	300	250	400	330	360	200	180
6	Money Out	50	60	55	35	40	40	45	35
7	Closing Balance	150	390	585	950	1240	1560	1715	1860
8
9	Month:	2
10	Opening Balance	950
11	Closing Balance	1860
Balances

Cell Formulas
Range	Formula
B10	B10	=HLOOKUP(B9,B2:I4,3,0)
B11	B11	=LOOKUP(B9+0.1,B2:I2,B7:I7)

 

[reacon84]

Oh wow! That was a speedy reply and exactly what I was after!

Thank you so much!

 

[Fluff]

Glad we could help & thanks for the feedback.