Using INDEX, IF, VLOOKUP OR MATCH? to return data from columns in a separate sheet - has complications...

[jayjayGC]

Hi There,

I am working on a sports tournament spreadsheet that copies specific data from various columns in a source worksheet (SUCCESSFUL) into a destination sheet (TEAM) based on an index/match function. I chose an INDEX/IF and then INDEX/MATCH function after much research and trial and error - however am still having trouble getting it to work. An example of the sheets are below.

TEAM sheet (destination)

Sport Coach Name Coach School Coach Phone Manager Name Manager School Manager Phone Manager Name Manager School Manager Phone
Cricket / Boys 11 & 12
Cricket / Boys 13-18
Cricket / Girls 11 & 12
Cricket / Girls 13-18
Hockey / Boys 11-18
Hockey / Girls 11-18
Rugby League / Boys 10-14
Rugby League / Boys 16-18
Rugby League / Girls 10-14
Rugby League / Girls 16-18


SUCCESSFULL Sheet (source)

First Name Surname School School Phone Email Mobile Phone Address State Sport Position
John Smith Coombabah SHS 55123456 jsmith@school 9876543 1 The Place QLD Cricket / Boys Under 15 Coach
Ned Jones Southport SHS 55234567 nj@school 8765432 2 The Close QLD Cricket / Boys Under 19 Coach
Susan Reid Miami SHS 55345678 sr@school 7654321 3 The Avenue QLD Cricket / Boys Under 15 Manager
Kelly Monteith Robina SHS 55456789 km@school 6543210 4 The Road QLD Rugby League / Boys Under 19 Coach
Conrad Smith Mudgeeraba SHS 55567890 cs@school 5432109 5 The Crescent QLD Rugby League / Girls Under 19 Manager
Brad Jones Keebra Park SHS 55678901 bj@school 4321098 6 The View QLD Hockey / Boys Under 15 Coach
Beth Reid Coomera SHS 55789012 br@school 3210987 7 The Street QLD Hockey / Boys Under 15 Coach
Belinda Kane Surfers SHS 55890123 bk@school 2109876 8 Lindae Street QLD Rugby League / Girls Under 19 Manager
Natalie Jones Benowa SHS 55901234 nj@school 1098765 9 Taliea Street QLD Rugby League / Girls Under 15 Trainer
Harry Smith Southport SHS 55234567 hs@school 1234567 2 Rrya Street QLD Rugby League / Boys Under 19 Manager


I used the following function for the coach name

=IFERROR(INDEX(Successful!$A$2:$A$1000&" "&Successful!$B$2:$B$1000,SMALL(IF((Successful!$T$2:$T$1000="Australian Football / Boys 10-12yrs")*(Successful!$U$2:$U$1000="Coach"),ROW(Successful!$2:$1000)-ROW(Successful!$1:$1)),ROW($A1))),"")

and it works fine, but it doesn't work for the manager name, I just get the coach's name again - I'm guessing its because that's the first record it finds? I did try changing it around to a MATCH function, as below, but keep getting errors.

=IFERROR(INDEX(Successful!$A$2:$A$300&” “&Successful!$B$2:$B$300,MATCH(1,(”Australian Football / Boys 10-12yrs”,Successful!$T$2:$T$300)*(“Manager”,Successful!$U$2:$U$300),0)),””)

Also, the source doc has the first and surname split into 2 different cells (A & B) so I had to join them together into one cell. T is the column where the sports names are located and column U contains the designation (coach/manager/trainer etc).

The difficult issues are as follows:

1) The sports names are different in each sheet so using a LOOKUP function requires adding another column, which I cannot do in the left most column of the TEAM sheet because it is designed to auto populate various word documents in an outside application, so therefore cannot be adjusted that way.

2) There is only one Coach and Trainer per team but there can be 2 Managers per team, so how do I populate the first "Manager" listing with the first instance from the SUCCESSFULL sheet and the 2nd "Manager" listing with the 2nd instance...

3) The SUCCESSFUL sheet data will change every year when new coaches, managers, convenors etc. are appointed, which may mean a change in the sports descriptions again (although that can be a problem for next year... )


Any help at all I can get would be very much appreciated - I have driven myself completely nuts over the last few days reading posts, watching tutorials and reading everything I can find... so THANK YOU in advance

Cheers,
Jen

 

[svendiamond]

Get the Mr Excel HTML Maker from this link: http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2545970

Follow the instructions to install the add-in and use it to copy and paste your data into the forum. It will come out like an actual spreadsheet instead of random blocks of text as you will see when you re-read your post. This will help us to visualize your data and help you solve your issue. Thanks!

 

[jayjayGC]

Hi,

Sorry, I did try to use that but it clearly didn't come out right... here it is again:

So this is the SUCCESSFUL sheet extract (not complete - sport and 1st choice are actually columns T & U respectively).

Excel 2012
	A	B	C	D	E	F	G	H	I
1	Given Name(s):	Surname:	Home address:	Suburb:	State:	Post code:	Home phone:	Sport	1st position choice:
2	Tara Lee	Alexander	87 The Peninsula?	Helensvale	QLD	4212	433061816	Cross Country / 13-19yrs	Manager
3	Tara Lee	Alexander	87 The Peninsula?	Helensvale	QLD	4212	433061816	Cross Country / 13-19yrs	Manager
4	Richard	Allan	?38CornubiaSt	Cornubia	QLD	4130		Track and Field / 13-19yrs	Coach
5	Jody	Allen	?42HillsboroughClose?	Robina	QLD	4226		Basketball / Boys 16-18yrs	Manager
6	Brett	Ansell	?25/1ManacorPlace?	Coombabah	Qld	4216		Touch / Boys 13-15yrs	Manager
7	Matthew	Armstrong	?6MagneticDr?	Eagle Heights	Qld	4271		Rugby League / Boys 10-12yrs	Trainer
8	Matthew	Armstrong	?6MagneticDr?	Eagle Heights	QLD	4271		Rugby Union / Boys 17-18yrs	Manager
9	Nicole	Aubrey	29/560 Gold Coast Highway ?	Tugun	QLD	4224		Surfing / 13-19yrs	Manager
10	Geoffrey	Bagnall	?18ParnikiPde	Palm beach	queensland	4221	55761550	Rugby League / Boys 16-18yrs	Convenor
11	Geoffrey	Bagnall	?18ParnikiPde	Palm beach	queensland	4221	55761550	Rugby League / Boys 16-18yrs	State Convenor
12	Leisa	Baker	?4/23 Labrador st,	Labrador	Qld	4215	418757304	Volleyball / Boys & Girls 12-15yr	Convenor
13	Leisa	Baker	?4/23 Labrador st,	Labrador	Qld	4215	418757304	Volleyball / Boys & Girls 16-19yrs	Convenor
Sheet1

The TEAM sheet is below (also not complete but you will get the idea):

Excel 2012
	A	B	C	D	E	F	G	H
1	Sport	Coach Name	Coach School Name	Coach School Phone	Manager Name	Manager School	Manager Mobile #	Manager School Phone
2	12 Yrs Boys Australian Football	Tara Lee Alexander	Helensvale State High School	5573 8555	Kevin Smith	Varsity College	0438 268 220	5562 3585
3	15 Yrs Boys Australian Football	Ben Tindall	Helensvale State High School	5573 8555	Kevin Smith	Coombabah State High School	0430 209 018	5552 3888
4	14 Yrs Boys Baseball	Kevin Fenn	Merrimac State High School	5595 8666	Andrew Tonkin	Miami State High School	0424 461 228	5554 0333
5	18 Yrs Boys Baseball	Kevin Fenn	Merrimac State High School	5595 8666	Ian Herd	Elanora State High School	0407 752 732	55684333
6	12 Yrs Boys Basketball	David Gurney	Varsity College	5562 3575	Matt O'Brien	Broadbeach State School	0438 200 787	5570 8444
7	12 Yrs Girls Basketball	Michelle Trinder	Windaroo State School	3382 4333	Karen Couch	All Saints Anglican School	0403 463 739	5530 2855
8	15 Yrs Boys Basketball	Tim Smith	Palm Beach Currumbin State High School	5525 9333	Brendan Smith	St Andrews Lutheran College	0428 822 482	5568 5900
9	15 Yrs Girls Basketball	Geoff MacLeod	Chisholm Catholic College	3209 0700	Shannon Shalhoub	Chisholm Catholic College	0408 979 807	3209 0700
10	18 Yrs Boys Basketball	Adam Darragh	Varsity College	5562 3575	Jody Allen	Miami State High School	0435 540 554	5554 0333
11	18 Yrs Girls Basketball	Adam Tyrus	Saint Stephens College	5573 8600	Jo Vann	Elanora State High School	0421 916 003	5568 4333
12	12 Yrs Boys Cricket	David Gee	Benowa State School	55104333	Megan Knowles	Silkwood School	0421 920 174	5565 0300
Sheet3

Thanks so much!

Cheers,
Jen

 

[svendiamond]

I'm sorry, what exactly are you trying to do? What would your expected results look like?

 

[jayjayGC]

Hi,

I am trying to get info from some of the columns in the SUCCESSFUL sheet into the corresponding columns on the TEAM sheet, such as Coach Name, Coach School etc.

So the TEAM sheet extract above is what it should look like but with the results having auto populated from the SUCCESSFUL sheet instead of being typed in manually.

I hope that explains things better.

Cheers,
Jen

 

[svendiamond]

I am trying to get info from some of the columns in the SUCCESSFUL sheet into the corresponding columns on the TEAM sheet

Which ones? Is the TEAM sheet already somewhat filled out? What does your TEAM sheet look like without any of the formulas you're trying to put in. Does it look like this?

Book1
	A	B	C	D	E	F	G	H
1	Sport	Coach Name	Coach School Name	Coach School Phone	Manager Name	Manager School	Manager Mobile #	Manager School Phone
2	12 Yrs Boys Australian Football
3	15 Yrs Boys Australian Football
4	14 Yrs Boys Baseball
5	18 Yrs Boys Baseball
6	12 Yrs Boys Basketball
7	12 Yrs Girls Basketball
8	15 Yrs Boys Basketball
9	15 Yrs Girls Basketball
10	18 Yrs Boys Basketball
11	18 Yrs Girls Basketball
12	12 Yrs Boys Cricket
TEAM

 

[jayjayGC]

Yes, that's what it looks like without the formulas. There are lots of columns that I need to copy over, but those are the main ones and I can work the others out if I can get these ones to work in the first place.

Thanks so much for your help - I'm very sorry if I'm not explaining things well...

 

[svendiamond]

On your TEAM sheet, the leagues are formatted like this:

	A
1	Sport
2	12 Yrs Boys Australian Football
3	15 Yrs Boys Australian Football
4	14 Yrs Boys Baseball
5	18 Yrs Boys Baseball
6	12 Yrs Boys Basketball
7	12 Yrs Girls Basketball
8	15 Yrs Boys Basketball
9	15 Yrs Girls Basketball
10	18 Yrs Boys Basketball
11	18 Yrs Girls Basketball
12	12 Yrs Boys Cricket

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>

TEAM​

But on your SUCCESSFUL sheet, the leagues are formatted like this:

	H
1	Sport
2	Cross Country / 13-19yrs
3	Cross Country / 13-19yrs
4	Track and Field / 13-19yrs
5	Basketball / Boys 16-18yrs
6	Touch / Boys 13-15yrs
7	Rugby League / Boys 10-12yrs
8	Rugby Union / Boys 17-18yrs
9	Surfing / 13-19yrs
10
11
12	Rugby League / Boys 16-18yrs
13
14
15	Rugby League / Boys 16-18yrs
16
17
18	Volleyball / Boys & Girls 12-15yr
19	Volleyball / Boys & Girls 16-19yrs

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>

SUCCESSFUL​

If they were formatted the same, this would be a lot easier. You could just use a formula like:

=INDEX(SUCCESSFUL!A:A&" "&SUCCESSFUL!B:B,MATCH(A2,SUCCESSFUL!H:H,0))

and similar formulas for the rest of the columns... or maybe you already know that, and your problem is when there are multiple matches? Is that it? We could probably fill out the entire TEAM sheet using VBA in that case. It would probably be easier than coming up with crazy INDEX/MATCH/SMALL/IF functions (although I know a few users on the forum who are skilled at creating them) -- can you post another example using all the columns on both sheets, or upload your file to DropBox, fast-files, or a similar file-sharing site?

 

[jayjayGC]

Yes, that is exactly the problem

To summarise, the issues are:

1. The TEAM sheet derives information from a number of sources and has around 60 columns of data, including some that are entered manually, and there are numerous external documents that are populated from that sheet - so I cannot fill it out completely in VBA
2. There are multiple matches across the data in the SUCCESSFUL sheet - the same person can be a coach, manager, trainer, convenor etc. in numerous sports. The only real unique field is the sport name, and that appears numerous times ... I guess the sport name and position added together would be unique, except for in the case of the Manager - there can be two of those

I could change the sport names in the SUCCESSFUL sheet to match the TEAM sheet ... it would have to be done manually with find/replace but that would be a small price to pay if it meant I could get the formulas to work.

That doesn't help my multiple matches issues though

The sheets are very large, do you still want me to upload them? I'm happy to but really don't want to waste your time any more than I have if it's getting that difficult.

Cheers,
Jen

 

[svendiamond]

Nah, don't upload your workbook.

Based on the two tables you provided, and assuming you change column A on the TEAM sheet to match the way the sport names are typed on the first table column H, how would I find the Coach School Name? Or Coach School Phone? Actually, how would I even find the Coach Name? Is it the name that matches "Coach" under column "I" and the sport under column "A"?