Help with IF formula

robgoldstein

Board Regular
Joined
Oct 26, 2013
Messages
146
Office Version
  1. 2019
Platform
  1. Windows
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
CDEFGHI
1clubgenderageGroupgrouprosterteamNamedivision
2BoysU12Boys U12Boys U12Markham SC BlueOne
3BoysU12Boys U12Boys U12Markham SC GreenTwo
4BoysU12Boys U12Boys U12Markham SC YellowThree
5GirlsU12Girls U12Girls U12Markham SC RedOne
6GirlsU12Girls U12Girls U12Markham SC OrangeTwo
7GirlsU12Girls U12Girls U12Markham SC YellowThree
8BoysU14Boys U14Boys U14Markham SC BlueOne
9BoysU14Boys U14Boys U14Markham SC GreenTwo
10BoysU14Boys U14Boys U14Markham SC YellowThree
11BoysU12Boys U12Boys U12Brampton Elite GrayOne
12BoysU12Boys U12Boys U12Brampton Elite YellowTwo
13BoysU12Boys U12Boys U12Brampton Elite WhiteThree
14BoysU12Boys U12Boys U12SC Toronto NavyOne
15BoysU12Boys U12Boys U12SC Toronto GrayTwo
16BoysU12Boys U12Boys U12SC Toronto RedThree
17BoysU12Boys U12Boys U12Pickering FC GrayOne
18BoysU12Boys U12Boys U12Pickering FC BlueTwo
19BoysU12Boys U12Boys U12Pickering FC YellowThree
20BoysU12Boys U12Boys U12Pickering FC WhiteThree
21GirlsU12Girls U12Girls U12SC Toronto PurpleOne
22GirlsU12Girls U12Girls U12SC Toronto OrangeTwo
23GirlsU12Girls U12Girls U12SC Toronto WhiteThree
24BoysU14Boys U14Boys U14Brampton Elite BlueOne
25BoysU14Boys U14Boys U14Brampton Elite SilverTwo
TeamImport Working
Cell Formulas
RangeFormula
F2:F25F2=D2&" "&E2
G2:G25G2=C2&" "&D2&" "&E2


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

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

JimM

Well-known Member
Joined
Nov 11, 2003
Messages
716
Can you post up the "Clubs" sheet as well so we can see an example of the relevant info
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,531
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=IFNA(LOOKUP(2,1/(SEARCH(Clubs!$A$2:$A$5,H2)),Clubs!$A$2:$A$5),"")
 

robgoldstein

Board Regular
Joined
Oct 26, 2013
Messages
146
Office Version
  1. 2019
Platform
  1. Windows
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

Board Regular
Joined
Oct 26, 2013
Messages
146
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

How about
Excel Formula:
=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.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,531
Office Version
  1. 365
Platform
  1. Windows
You can just change the $A$2:$A$5 to whatever range the range is on the clubs sheet.
 

robgoldstein

Board Regular
Joined
Oct 26, 2013
Messages
146
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,531
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=IFNA(LOOKUP(2,1/((SEARCH(Clubs!$A$2:$A$10,H2))*(Clubs!$A$2:$A$10<>"")),Clubs!$A$2:$A$10),"")
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,531
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,498
Messages
5,636,672
Members
416,935
Latest member
Atulcp

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