Searching Two Columns

S

Steve 1962

Active Member
Joined
Jan 3, 2006
Messages
343
Office Version
  1. 365
Platform
  1. Windows
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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
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
 
Upvote 0
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:
Upvote 0
Thanks Fluff. Just a query -

Is there a solution if I don't have the FILTER function ?
 
Upvote 0
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.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Excel pivot table query - retaining filters on pivot in absence of data
2
Replies
10
Views
920
Alex Blakenburg
A
S
  • Solved
Rank a list
Replies
8
Views
435
Fluff
Fluff
N
  • Question
Two different tables - 4 columns each
Replies
8
Views
238
Fluff
Fluff
V
  • Question
Searching set of rows and multi columns for a value
Replies
5
Views
545
HongRu
HongRu
A
  • Solved
Search a value across a range of two columns and return the first 10 results
2
Replies
13
Views
531
Abdmujib
A

Forum statistics

Threads
1,214,378
Messages
6,119,188
Members
448,873
Latest member
jacksonashleigh99

Share this page

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
Back
Top