Need some advise on using Index/Match or Lookup

[Frank3923]

My Excel skills are a bit rusty. Looking for some assistance on using Index/Match or VLookup. I would like to get the corresponding number from Team No. A10 - A19 and match it to the teams listed in B3-7, and F3-7. Any consideration and help would be appreciated.
Thank you in advance.

FB-Test.xls
	A	B	C	D	E	F	G	H
1	Team-No		Team		Team-No
2		AWAY TEAM	AS	Week		HOME TEAM	HS	Week
3		Tennessee (3-9-0)	3	13		Indianapolis (12-0-0)	35	13
4		Green Bay (2-10-0)	7	13		Chicago (9-3-0)	19	13
5		Atlanta (7-5-0)	6	13		Carolina (9-3-0)	24	13
6		Cincinnati (9-3-0)	38	13		Pittsburgh (7-5-0)	31	13
7		Tampa Bay (8-4-0)	10	13		New Orleans (3-9-0)	3	13
8
9		TEAM
10	2	Atlanta
11	5	Carolina
12	6	Chicago
13	12	Green Bay
14	14	Indianapolis
15	20	New Orleans
16	25	Pittsburgh
17	30	Tampa Bay
18	31	Tennessee
19	32	Washington
Sheet1

 

[Frank3923]

Yogi - I did check the references, they seem to be correct, As you suggested, here is my work sheet.

Thank you in advance, for your help.

FB-Test.xls
	A	B	C	D	E	F	G	H
1	Team-No		Team		Team-No
2		AWAY TEAM	AS	Week		HOME TEAM	HS	Week
3	#N/A	Tennessee (3-9-0)	3	13	#N/A	Indianapolis (12-0-0)	35	13
4	#N/A	Green Bay (2-10-0)	7	13	#N/A	Chicago (9-3-0)	19	13
5	#N/A	Atlanta (7-5-0)	6	13	#N/A	Carolina (9-3-0)	24	13
6	#N/A	Cincinnati (9-3-0)	38	13	#N/A	Pittsburgh (7-5-0)	31	13
7	#N/A	Tampa Bay (8-4-0)	10	13	#N/A	New Orleans (3-9-0)	3	13
8
9		TEAM
10	2	Atlanta
11	5	Carolina
12	6	Chicago
13	12	Green Bay
14	14	Indianapolis
15	20	New Orleans
16	25	Pittsburgh
17	30	Tampa Bay
18	31	Tennessee
19	32	Washington
Sheet1

 

[XLGibbs]

YOu are attempting to Match

Team Name (Record) with Team Name

The result is always Team Name (Record) <> Team Name

Where Yogi's example was Team Name = Team Name

You can change the formula to:

=INDEX($A$10:$A$19,MATCH(
Trim(Left(B3, FIND("(") -1)),$B$10:$B$19,0))

The key being this:
Trim(Left(B3, FIND("(",B3) -1))

Which will return the team name appearing left of the open parenthesis less any trailing spaces.

Untested, but should work.

Edit: I didn't see that Fairwinds had already posted this potential solution.