Need some advise on using Index/Match or Lookup

Frank3923

Board Regular
Joined
Jan 20, 2003
Messages
244
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
ABCDEFGH
1Team-NoTeamTeam-No
2AWAY TEAMASWeekHOME TEAMHSWeek
3Tennessee (3-9-0)313Indianapolis (12-0-0) 3513
4Green Bay (2-10-0)713Chicago (9-3-0) 1913
5Atlanta (7-5-0)613Carolina (9-3-0) 2413
6Cincinnati (9-3-0) 3813Pittsburgh (7-5-0)3113
7Tampa Bay (8-4-0) 1013New Orleans (3-9-0)313
8
9TEAM
102Atlanta
115Carolina
126Chicago
1312Green Bay
1414Indianapolis
1520New Orleans
1625Pittsburgh
1730Tampa Bay
1831Tennessee
1932Washington
Sheet1
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

gaj104

Well-known Member
Joined
Nov 9, 2002
Messages
864
Id suggest making a new A column. Trim the team number using left() function, and then a vlookup. As illustrated.
Book2
ABCDEFGHIJ
1AbbrvTeam-NoTeamTeam-NoTeam-No
2AWAY TEAMASWeekHOME TEAMHSWeek
331Tennessee (3-9-0)31314Indianapolis (12-0-0) 3513
412Green Bay (2-10-0)7136Chicago (9-3-0) 1913
52Atlanta (7-5-0)6135Carolina (9-3-0) 2413
66Cincinnati (9-3-0) 381325Pittsburgh (7-5-0)3113
730Tampa Bay (8-4-0) 101320New Orleans (3-9-0)313
8
9TEAM
10Atlan2Atlanta
11Carol5Carolina
12Chica6Chicago
13Green12Green Bay
14India14Indianapolis
15New O20New Orleans
16Pitts25Pittsburgh
17Tampa30Tampa Bay
18Tenne31Tennessee
19Washi32Washington
Sheet1
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi,

Try:

=INDEX($A$10:$A$30,MATCH(LEFT(B3,FIND("(",B3)-2),$B$10:$B$30,0))

in E3 and drag down.
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi Frank3923:

How about ...
Book2
ABCDEF
1Team
2AWAYHOME
331Tennessee14Indianapolis
412GreenBay6Chicago
52Atlanta5Carolina
6#N/ACincinnati25Pittsburgh
730TampaBay20NewOrleans
8
9TEAM
102Atlanta
115Carolina
126Chicago
1312GreenBay
1414Indianapolis
1520NewOrleans
1625Pittsburgh
1730TampaBay
1831Tennessee
1932Washington
20
Sheet1


formula in cell a3 is ... =INDEX($A$10:$A$19,MATCH(B3,$B$10:$B$19,0))

this is then copied to cells A3:A7 and E3:E7
 

Frank3923

Board Regular
Joined
Jan 20, 2003
Messages
244

ADVERTISEMENT

Thank you to all that responded, I appreciate your time and effort.
 

Frank3923

Board Regular
Joined
Jan 20, 2003
Messages
244
Yogi, I tried your method as instructed, however when I paste into my sheet, I am getting a #N/A error. Any suggestions, as to what I doing wrong.

Frank
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454

ADVERTISEMENT

Frank3923 said:
Yogi, I tried your method as instructed, however when I paste into my sheet, I am getting a #N/A error. Any suggestions, as to what I doing wrong.

Frank
Hi Frank3923:

I can not say what might be wrong -- perhaps some problem with entering the data or the formulas. Let me ask ... are you able to reproduce the results that I have in my post (with the same data and the formulas as in my post)?
 

Frank3923

Board Regular
Joined
Jan 20, 2003
Messages
244
Yogi
No, I am not able to reproduce as in example, I did cut and paste from your answer, into my sheet. I then received the error. I even tried entering the formula, and still received the same response. It has me baffled, how it could work in your repsonse to my example, and not on the actual sheet.
 

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
Make sure the numbers in your excel sheet are formatted as numbers.

If you pasted data from the web page, it is possible the values are being seen by excel as text.
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Frank3923 said:
Yogi
No, I am not able to reproduce as in example, I did cut and paste from your answer, into my sheet. I then received the error. I even tried entering the formula, and still received the same response. It has me baffled, how it could work in your repsonse to my example, and not on the actual sheet.
Hi Frank:

If you are cutting and pasting the formulas from my post into your worksheet, are cell references in your worksheet consistent with those in my post?

There has got to be a logical explanation ... you may want to post related part of your worksheet on the Board using HTMLmaker, so we can see what exactly is happening!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,447
Messages
5,572,152
Members
412,446
Latest member
jorgefelipe
Top