Index/ lookup function

[Jzfbkr]

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]RANK[/TD]
[TD]TEAM NAME[/TD]
[TD]Played[/TD]
[TD]Points[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]TEAM A[/TD]
[TD]4[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]TEAM B[/TD]
[TD]4[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]TEAM C[/TD]
[TD]3[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]TEAM D[/TD]
[TD]4[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]










TEAM INPUT CELL[TABLE="class: grid, width: 100, align: center"]
<tbody>[TR]
[TD]TEAM C[/TD]
[/TR]
</tbody>[/TABLE]

I am looking to try and find the TEAM NAME in the input cell within the range above and replicate the data above for the row above and below returning the following in another location:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Rank[/TD]
[TD]TEAM NAME[/TD]
[TD]Played[/TD]
[TD]Points[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]TEAM B[/TD]
[TD]4[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]TEAM C[/TD]
[TD]3[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]TEAM D[/TD]
[TD]4[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]








Does anyone have any suggestions?

Thanks

 

[mole999]

would it always be B above and D below, selected only by Team name ?

 

[Jzfbkr]

It would move according to the team name.

(I.E) if I entered the team name of a team in position 10 it would return the information for positions 9 and 11

 

[AhoyNC]

See if this works.
Note that the IFERROR part of the formula only works in Excel 2007 or later.

Formulas in A10 and A12 would be copied across cells.

Excel Workbook
	A	B	C	D
1	RANK	TEAM NAME	Played	Points
2	1	TEAM A	4	9
3	2	TEAM B	4	8
4	3	TEAM C	3	7
5	4	TEAM D	4	7
6
7
8		TEAM C
9	Rank	TEAM NAME	Played	Points
10	2	TEAM B	4	8
11	3	TEAM C	3	7
12	4	TEAM D	4	7
Sheet