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

 

[jayjayGC]

Yeah, I left some of the columns out of the SUCCESSFUL sheet when I posted it - there is so much in there I didn't want to waste space.

So there are columns in the SUCCESSFUL sheet that include coach first name, coach surname, coach school and then obviously the same for manager, trainer, convenor etc.

It's those columns I want copied over to the corresponding columns in the TEAM sheet.

And I'll need to change the sport names in the SUCCESSFUL sheet to match those in the TEAM sheet - the TEAM sheet needs to stay the way it is because of the external documents settings that it auto populates to.

So the two sheets are below:

The Successful Sheet (Source)

Excel 2012
	A	B	C	D	E	M	T	U
1	Given Name(s):	Surname:	Home address:	Suburb:	State:	School:	Sport	1st position choice:
2	Tara Lee	Alexander	87 The Peninsula?	Helensvale	QLD	St Michaels College	Cross Country / 13-19yrs	Manager
3	Tara Lee	Alexander	87 The Peninsula?	Helensvale	QLD	St Michaels College	Cross Country / 13-19yrs	Manager
4	Richard	Allan	?38CornubiaSt	Cornubia	QLD		Track and Field / 13-19yrs	Coach
5	Jody	Allen	?42HillsboroughClose?	Robina	QLD	Miami State High School	Basketball / Boys 16-18yrs	Manager
6	Brett	Ansell	?25/1ManacorPlace?	Coombabah	Qld	Oxenford State School	Touch / Boys 13-15yrs	Manager
7	Matthew	Armstrong	?6MagneticDr?	Eagle Heights	Qld	St Josephs Tobruk Memorial School	Rugby League / Boys 10-12yrs	Trainer
8	Matthew	Armstrong	?6MagneticDr?	Eagle Heights	QLD	St Josephs Tobruk Memorial School	Rugby Union / Boys 17-18yrs	Manager
9	Nicole	Aubrey	29/560 Gold Coast Highway ?	Tugun	QLD	Pacific Pines State High School	Surfing / 13-19yrs	Manager
10	Geoffrey	Bagnall	?18ParnikiPde	Palm beach	queensland	Palm Beach-Currumbin State High School	Rugby League / Boys 16-18yrs	Convenor
11	Geoffrey	Bagnall	?18ParnikiPde	Palm beach	queensland	Palm Beach-Currumbin State High School	Rugby League / Boys 16-18yrs	State Convenor
12	Leisa	Baker	?4/23 Labrador st,	Labrador	Qld	Pacific Pines State High School	Volleyball / Boys & Girls 12-15yr	Convenor
13	Leisa	Baker	?4/23 Labrador st,	Labrador	Qld	Pacific Pines State High School	Volleyball / Boys & Girls 16-19yrs	Convenor
Successful

and the TEAM sheet (destination):

Excel 2012
	B	O	P	Q	R	S	T
1	Sport	Coach Name	Coach School Name	Coach School Phone	Coach School Email	Manager Name	Manager School
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 (2)

I hope this helps a bit more ... thanks so much for your patience and help!

 

[svendiamond]

Can't you just use INDEX/MATCH formulas for all of them? If there are multiple matches and your question is how to get multiple matches using INDEX/MATCH, make a new post about that with a simple example, or google "INDEX MATCH multiple results" and you will find plenty of explanations and solutions.

 

[jayjayGC]

That's why I posted here in the first place, because I'd spent days trying every possible mix of index/match/if/vlookup combination that I had seen during numerous searches, and still couldn't get them to work.

Thanks for your help - I'll just keep trying.

 

[svendiamond]

jayjay, I would love to help. But in your example second table, the columns you want to lookup are not in the example first table. I don't know what formula to write to look up the information because of this. If you're going to provide a small selection of your data, show the relevant data so your issue can be resolved. The format for your typical INDEX/MATCH is

=INDEX(returnRange,MATCH(lookupValue,lookupRange,0))

and you still didn't specify if the "multiple matches" is your main issue.