Help with IF formula

[robgoldstein]

I have a sheet named "Team Import" that has a list of teams and other information about those teams. Within the team name is the club name. I also have a list of Club names on a different sheet called "Clubs" I want to fill the Club column on the "Team import" sheet with the Club name from the list on the Clubs sheet if the name of the club appears in the team name on that column. Here is the sheet I am trying to fill. The club names are in column "A" in the "Clubs" sheet.

League Link Uploader Template.xlsm
	C	D	E	F	G	H	I
1	club	gender	ageGroup	group	roster	teamName	division
2		Boys	U12	Boys U12	Boys U12	Markham SC Blue	One
3		Boys	U12	Boys U12	Boys U12	Markham SC Green	Two
4		Boys	U12	Boys U12	Boys U12	Markham SC Yellow	Three
5		Girls	U12	Girls U12	Girls U12	Markham SC Red	One
6		Girls	U12	Girls U12	Girls U12	Markham SC Orange	Two
7		Girls	U12	Girls U12	Girls U12	Markham SC Yellow	Three
8		Boys	U14	Boys U14	Boys U14	Markham SC Blue	One
9		Boys	U14	Boys U14	Boys U14	Markham SC Green	Two
10		Boys	U14	Boys U14	Boys U14	Markham SC Yellow	Three
11		Boys	U12	Boys U12	Boys U12	Brampton Elite Gray	One
12		Boys	U12	Boys U12	Boys U12	Brampton Elite Yellow	Two
13		Boys	U12	Boys U12	Boys U12	Brampton Elite White	Three
14		Boys	U12	Boys U12	Boys U12	SC Toronto Navy	One
15		Boys	U12	Boys U12	Boys U12	SC Toronto Gray	Two
16		Boys	U12	Boys U12	Boys U12	SC Toronto Red	Three
17		Boys	U12	Boys U12	Boys U12	Pickering FC Gray	One
18		Boys	U12	Boys U12	Boys U12	Pickering FC Blue	Two
19		Boys	U12	Boys U12	Boys U12	Pickering FC Yellow	Three
20		Boys	U12	Boys U12	Boys U12	Pickering FC White	Three
21		Girls	U12	Girls U12	Girls U12	SC Toronto Purple	One
22		Girls	U12	Girls U12	Girls U12	SC Toronto Orange	Two
23		Girls	U12	Girls U12	Girls U12	SC Toronto White	Three
24		Boys	U14	Boys U14	Boys U14	Brampton Elite Blue	One
25		Boys	U14	Boys U14	Boys U14	Brampton Elite Silver	Two
TeamImport Working

Cell Formulas
Range	Formula
F2:F25	F2	=D2&" "&E2
G2:G25	G2	=C2&" "&D2&" "&E2

any help you can provide would really be appreciated.
thanks.

 

[JimM]

Can you post up the "Clubs" sheet as well so we can see an example of the relevant info

 

[Fluff]

How about

=IFNA(LOOKUP(2,1/(SEARCH(Clubs!$A$2:$A$5,H2)),Clubs!$A$2:$A$5),"")

 

[robgoldstein]

sorry, i posted the wrong 2nd sheet.

Can you post up the "Clubs" sheet as well so we can see an example of the relevant info

sorry Jim.

Club Names
Markham SC
Brampton Elite
SC Toronto
Pickering FC

 

[robgoldstein]

How about

=IFNA(LOOKUP(2,1/(SEARCH(Clubs!$A$2:$A$5,H2)),Clubs!$A$2:$A$5),"")

Thank you so much. That works perfectly, but this is just sample data. I would need the list to search the entire column A in the Clubs sheet. When I try and change it I get "0" returned.

 

[Fluff]

You can just change the $A$2:$A$5 to whatever range the range is on the clubs sheet.

 

[robgoldstein]

You can just change the $A$2:$A$5 to whatever range the range is on the clubs sheet.

so is there a way to make this work nomatter how many entries I have? I am going to reuse this sheet with different datasets so different numbers of teams.

 

[Fluff]

How about

=IFNA(LOOKUP(2,1/((SEARCH(Clubs!$A$2:$A$10,H2))*(Clubs!$A$2:$A$10<>"")),Clubs!$A$2:$A$10),"")

 

[robgoldstein]

How about

=IFNA(LOOKUP(2,1/((SEARCH(Clubs!$A$2:$A$10,H2))*(Clubs!$A$2:$A$10<>"")),Clubs!$A$2:$A$10),"")

Fluff, you are a hero. Thanks so much.

 

[Fluff]

You're welcome & thanks for the feedback.