# Filter a list, source list is in 2 columns



## coinbank (Dec 14, 2022)

I am running a college football pool and I want to get a list of all of the teams that are playing in the games this year.  I want to return the names of the teams in column K & M, in one column (separate schedule) if Column C is "2022".  Is there a way to use the filter to pull from both columns?

So on my work computer it won't allow me to install the Mini-Sheet however here is the sheet:


CDEFGHIJKLMYearPlayoff/ ChampBowlLocationDateTimeNetworkFave RankFavoriteDog RankUnderdog2022BahamasNassau, Bahamas12/16/202211:30 AMESPNUABMiami (Ohio)2022CureOrlando, FL12/16/20223:00 PMESPN24Troy25UTSA2022FenwayBoston, MA12/17/202211:00 AMESPNLouisvilleCincinnati2022CelebrationAtlanta, GA12/17/202212:00 PMABCJackson StateNC Central2022New MexicoAlbuquerque, NM12/17/20222:15 PMESPNSMUBYU2022Jimmy KimmelLos Angeles, CA12/17/20223:30 PMABCFresno StateWashington State2022Lending TreeMobile, AL12/17/20225:45 PMESPNSouthern MissRice2022Las VegasLas Vegas, NV12/17/20227:30 PMESPN14Oregon StateFlorida2022FriscoFrisco, TX12/17/20229:15 PMESPNBoise StateNorth Texas2022Myrtle BeachConway, SC12/19/20222:30 PMESPNMarshallConnecticut2022Famous Idaho PotatoBoise, ID12/20/20223:30 PMESPNSan Jose StateEastern Michigan2022Boca RatonBoca Raton, FL12/20/20227:30 PMESPNToledoLiberty2022New OrleansNew Orleans, LA12/21/20229:00 PMESPNSouth AlabamaWestern Kentucky2022Armed ForcesFort Worth, TX12/22/20227:30 PMESPNBaylorAir Force


----------



## fjns (Dec 21, 2022)

Hi, see the linked files (with 5 auxiliary columns) for a possible solution for Office2007 and Office2010...

The formulas used in the table:
A2: =INDEX(Source!C:C,ROW())
B2: =INDEX(Source!K:K,ROW())
C2: =INDEX(Source!M:M,ROW())
D2: =IF(A2=2022,1,0)
E2: =IF(A2=2022,SUM(D$2:D2)+1,0)
G1: =MAX(E:E)
I2: =IF(ROW()>G$1,"",INDEX(B:B,MATCH(ROW(),E:E,0))&" & "&INDEX(C:C,MATCH(ROW(),E:E,0)))

CollegeFootball.xlsx
CollegeFootball2.xlsx


----------

