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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,997
Messages
5,834,799
Members
430,322
Latest member
excelnoobnoob

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top