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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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
 
Upvote 0
Hi,

Try:

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

in E3 and drag down.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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)?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,568
Messages
6,114,348
Members
448,570
Latest member
rik81h

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
Back
Top