Excel: looking a Variable data from row 1 then return data 3 rows below formula.

[andy0o0o]

I am having trouble coming up with a formula that I need, I'm seeking help to see if one of you guys at help me with. I have attached a screen shot of the table and the "worded" formula. http://img.photobucket.com/albums/v332/andy0o0o/IMG_5118.gif

in another sheet I want to pick up the data from the table below with the
Tom's ending balance of Column B in one cell and Column C in another
Jeff's Ending balance of Column B in one cell and Column C in another

the ending balance line will always be 3 row under the person's name but the row between each person are not always 4 row under. I tried offset function but it always need a reference point which can't be used in this situation.

Table

	A	B	C
1	TOM
2	beg bal	11	200
3	act	2	20
4	end bal	13	220
5	Jeff
6	beg bal	25	154
7	act	100	45
8	end bal	125	200
9

<tbody>
</tbody>

 

[63falcondude]

Try something like this:

Excel 2010
	A	B	C	D	E	F	G
1	TOM				TOM	13	220
2	beg bal	11	200
3	act	2	20
4	end bal	13	220
5	Jeff
6	beg bal	25	154
7	act	100	45
8	end bal	125	200
Sheet2

Cell Formulas
Range	Formula
F1	=INDEX(B1:B8,MATCH($E$1,$A$1:$A$8,0)+3)

Drag the formula to the right. Edit ranges as needed.

 

[andy0o0o]

Try something like this:

Excel 2010

	A	B	C	D	E	F	G
1	TOM				TOM	13	220
2	beg bal	11	200
3	act	2	20
4	end bal	13	220
5	Jeff
6	beg bal	25	154
7	act	100	45
8	end bal	125	200

<colgroup><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula F1 =INDEX(B1:B8,MATCH($E$1,$A$1:$A$8,0)+3) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Drag the formula to the right. Edit ranges as needed.

Thanks that worked so far however I ran into another issue, as I state before that ""the ending balance line will always be 3 row under the person's name"" this statement is not true as I come to find as I looked further. it could be like the table below - is there a remedy for that?

	a	b	c	d	e
1	tom
2	beg bal
3	act
4	end bal
5	jeff
6	beg bal
7	empty line	empty line	empty line
8	act
9	end bal
10
11	jone
12	empty line	empty line	empty line
13	empty line	empty line	empty line
14	beg bal....

<tbody>
</tbody>