Searching Two Columns

Steve 1962

Active Member
Joined
Jan 3, 2006
Messages
293
Office Version
  1. 365
Hi

I am tracking football scores but depending on the draw, there are two team columns and teams can be in either of the two columns (with their respective scores). In the example, this is columns B and D. However, these teams can alternate between either column which is making searching difficult. I would like to display the result as they are in rows 29 to 31 by placing the search criteria in cell A29 (the round number) and cell B29 (team name) to produce their score in cells C29 to C31.

Thanks

Book1
ABCDE
1RoundTeam_For1For1Team_For2For2
21Eels8Bulldogs2
31Raiders24Titans6
41Cowboys21Broncos28
51Knights20Warriors0
61Rabbitohs22Sharks18
71Panthers20Roosters14
81Sea Eagles4Storm18
91Dragons14Tigers24
102Bulldogs16Cowboys24
112Dragons28Panthers32
122Broncos22Rabbitohs18
132Warriors6Raiders20
142Roosters8Sea Eagles9
152Sharks10Storm12
162Tigers24Knights42
172Titans6Eels46
183Broncos6Eels34
193Cowboys36Titans6
203Roosters28Rabbitohs12
213Warriors18Dragons0
223Sharks16Tigers28
233Storm6Raiders22
243Panthers14Knights14
253Sea Eagles32Bulldogs6
26
27
28RoundTeamScore
291Raiders24
302Raiders20
313Raiders22
Sheet8
Cell Formulas
RangeFormula
A30:A31A30=A29+1
B30:B31B30=B29
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Steve 1962

Active Member
Joined
Jan 3, 2006
Messages
293
Office Version
  1. 365
Just an addition to the above -

Would like to have the opposing team and their score also adjacent.

Thanks


Book1
HIJKL
1RoundTeam_For1For1Team_For2For2
21Eels8Bulldogs2
31Raiders24Titans6
41Cowboys21Broncos28
51Knights20Warriors0
61Rabbitohs22Sharks18
71Panthers20Roosters14
81Sea Eagles4Storm18
91Dragons14Tigers24
102Bulldogs16Cowboys24
112Dragons28Panthers32
122Broncos22Rabbitohs18
132Warriors6Raiders20
142Roosters8Sea Eagles9
152Sharks10Storm12
162Tigers24Knights42
172Titans6Eels46
183Broncos6Eels34
193Cowboys36Titans6
203Roosters28Rabbitohs12
213Warriors18Dragons0
223Sharks16Tigers28
233Storm6Raiders22
243Panthers14Knights14
253Sea Eagles32Bulldogs6
26
27
28RoundTeamScoreTeamScore
291Raiders24Titans6
302Raiders20Warriors6
313Raiders22Storm6
Sheet8
Cell Formulas
RangeFormula
H30:H31H30=H29+1
I30:I31I30=I29
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,246
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff New.xlsm
ABCDE
1RoundTeam_For1For1Team_For2For2
21Eels8Bulldogs2
31Raiders24Titans6
41Cowboys21Broncos28
51Knights20Warriors0
61Rabbitohs22Sharks18
71Panthers20Roosters14
81Sea Eagles4Storm18
91Dragons14Tigers24
102Bulldogs16Cowboys24
112Dragons28Panthers32
122Broncos22Rabbitohs18
132Warriors6Raiders20
142Roosters8Sea Eagles9
152Sharks10Storm12
162Tigers24Knights42
172Titans6Eels46
183Broncos6Eels34
193Cowboys36Titans6
203Roosters28Rabbitohs12
213Warriors18Dragons0
223Sharks16Tigers28
233Storm6Raiders22
243Panthers14Knights14
253Sea Eagles32Bulldogs6
26
27
28RoundTeamScore
291Raiders24Titans6
302Raiders20Warriors6
313Raiders22Storm6
Main
Cell Formulas
RangeFormula
C29:E31C29=FILTER($C$2:$E$25,($A$2:$A$25=A29)*($B$2:$B$25=B29),INDEX(FILTER($B$2:$E$25,($A$2:$A$25=A29)*($D$2:$D$25=B29)),,{4,1,2}))
A30:A31A30=A29+1
B30:B31B30=B29
Dynamic array formulas.
 
Last edited:

Steve 1962

Active Member
Joined
Jan 3, 2006
Messages
293
Office Version
  1. 365

ADVERTISEMENT

Thanks Fluff. Just a query -

Is there a solution if I don't have the FILTER function ?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,246
Office Version
  1. 365
Platform
  1. Windows
Yes, you can use
+Fluff New.xlsm
ABCDE
1RoundTeam_For1For1Team_For2For2
21Eels8Bulldogs2
31Raiders24Titans6
41Cowboys21Broncos28
51Knights20Warriors0
61Rabbitohs22Sharks18
71Panthers20Roosters14
81Sea Eagles4Storm18
91Dragons14Tigers24
102Bulldogs16Cowboys24
112Dragons28Panthers32
122Broncos22Rabbitohs18
132Warriors6Raiders20
142Roosters8Sea Eagles9
152Sharks10Storm12
162Tigers24Knights42
172Titans6Eels46
183Broncos6Eels34
193Cowboys36Titans6
203Roosters28Rabbitohs12
213Warriors18Dragons0
223Sharks16Tigers28
233Storm6Raiders22
243Panthers14Knights14
253Sea Eagles32Bulldogs6
26
27
28RoundTeamScore
291Raiders24Titans6
302Raiders20Warriors6
313Raiders22Storm6
Main
Cell Formulas
RangeFormula
C29:C31C29=IFERROR(INDEX(C$2:C$25,MATCH($A29&"|"&$B29,$A$2:$A$25&"|"&$B$2:$B$25,0)),INDEX($E$2:$E$25,MATCH($A29&"|"&$B29,$A$2:$A$25&"|"&$D$2:$D$25,0)))
D29:E31D29=IFERROR(INDEX(D$2:D$25,MATCH($A29&"|"&$B29,$A$2:$A$25&"|"&$B$2:$B$25,0)),INDEX(B$2:B$25,MATCH($A29&"|"&$B29,$A$2:$A$25&"|"&$D$2:$D$25,0)))
A30:A31A30=A29+1
B30:B31B30=B29
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Fluff

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

Watch MrExcel Video

Forum statistics

Threads
1,132,914
Messages
5,655,937
Members
418,253
Latest member
TheJackal26

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