Look up values in a row and pull corresponding datat to a cell in the same row

S

senechia

New Member
Joined
Sep 18, 2009
Messages
6
The attached is a game schedule for a pool league. I'm trying to pull the data from the Game Table to the Team Table to the corresponding matches. Eg. If i want to pull in the opponent From Game 1 on 4/11(7 vs 3) and put the opponent 3 under 7 and also put opponent 7 under 3

DateGame 1Game 2Game 3Game412345678
2/7/20191vs23vs45vs67vs8
2/14/20196vs85vs72vs41vs3
2/21/20195vs41vs87vs32vs6
2/28/20193vs67vs21vs58vs4
3/7/20197vs14vs63vs85vs2
3/14/20192vs38vs54vs16vs7How do I return the values from the table on the left to the table above?
3/21/20194vs76vs18vs23vs5
3/28/20195vs67vs83vs41vs2
4/4/20192vs41vs35vs76vs8
4/11/20197vs32vs61vs85vs4Example:73
4/18/20191vs58vs47vs23vs6
4/25/20193vs85vs24vs67vs1

<tbody>
</tbody>
 
Last edited:

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
​Put the game number in cell P1

Hoja1

ABCDEFGHIJKLM
1
2DateGame 1Game 2Game 3Game4
302/07/20191vs23vs45vs67vs8
42/14/20196vs85vs72vs41vs3
52/21/20195vs41vs87vs32vs6
62/28/20193vs67vs21vs58vs4
703/07/20197vs14vs63vs85vs2
83/14/20192vs38vs54vs16vs7
93/21/20194vs76vs18vs23vs5
103/28/20195vs67vs83vs41vs2
1104/04/20192vs41vs35vs76vs8
1204/11/20197vs32vs61vs85vs4
134/18/20191vs58vs47vs23vs6
144/25/20193vs85vs24vs67vs1

<tbody>
</tbody>


Hoja1

OPQRSTUV
1Game1
212345678
321
4 8 6
5 54
6 6 3
77 1
8 32
9 7 4
10 65
11 4 2
12 7 3
135 1
14 8 3

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:43px;"><col style="width:43px;"><col style="width:43px;"><col style="width:43px;"><col style="width:43px;"><col style="width:43px;"><col style="width:43px;"><col style="width:43px;"></colgroup><tbody>
</tbody>



CellFormula
O3=IF(OFFSET($A3,0,($P$1*3)-2)=O$2,OFFSET($A3,0,($P$1*3)),IF(OFFSET($A3,0,($P$1*3))=O$2,OFFSET($A3,0,($P$1*3)-2),""))

<tbody>
</tbody>
 
Upvote 0
Another possible take, row 4 is the first row of results, formula posted at the bottom just copy to fill in the rest of the table.

Col ACol Q
Game 1
DateGame 1Game 2Game 3 Game412345678
02/07/20191vs2 3vs4 5vs6 7vs821
2/14/20196vs8 5vs7 2vs4 1vs3 8 6
2/21/20195vs4 1vs8 7vs3 2vs6 54
2/28/20193vs6 7vs2 1vs5 8vs4 6 3
03/07/20197vs1 4vs6 3vs8 5vs27 1
3/14/20192vs3 8vs5 4vs1 6vs7 32
3/21/20194vs7 6vs1 8vs2 3vs5 7 4
3/28/20195vs6 7vs8 3vs4 1vs2 65
04/04/20192vs4 1vs3 5vs7 6vs8 4 2
04/11/20197vs3 2vs6 1vs8 5vs4 7 3
4/18/20191vs5 8vs4 7vs2 3vs65 1
4/25/20193vs8 5vs2 4vs6 7vs1 8 3
Formula in Q4: =IFNA(INDEX($B4:$D4,1,MATCH(Q$3,$B4:$D4,0)+IF($B4=Q$3,2,-2)),"")

<colgroup><col><col><col><col><col><col><col><col><col><col span="3"><col><col span="3"><col><col><col><col span="3"><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
I need all values in every feild for every game. Not just for the game in P1. Every cell in the table should be filled all the way up, down and across. Is there a way to do that? Thanks for your efforts
 
Upvote 0
Example would be for 2/7/19 there would be 8 teams playing and the formulas in that row need to be completed
 
Upvote 0
Try:
In Cell O3

=IF($B3=O$2,$D3,IF($E3=O$2,$G3,IF($H3=O$2,$J3,IF($K3=O$2,$M3,IF($D3=O$2,$B3,IF($G3=O$2,$E3,IF($J3=O$2,$H3,IF($M3=O$2,$K3,""))))))))




Copy the formula down and to the right.


Hoja1

ABCDEFGHIJKLMNOPQRSTUV
1
2DateGame 1 Game 2 Game 3 Game4 12345678
302/07/20191vs23vs45vs67vs8 21436587
42/14/20196vs85vs72vs41vs3 34127856
52/21/20195vs41vs87vs32vs6 86754231
62/28/20193vs67vs21vs58vs4 57681324
703/07/20197vs14vs63vs85vs2 75862413
83/14/20192vs38vs54vs16vs7 43218765
93/21/20194vs76vs18vs23vs5 68573142
103/28/20195vs67vs83vs41vs2 21436587
1104/04/20192vs41vs35vs76vs8 34127856
1204/11/20197vs32vs61vs85vs4 86754231
134/18/20191vs58vs47vs23vs6 57681324
144/25/20193vs85vs24vs67vs1 75862413

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:75px;"><col style="width:23px;"><col style="width:23px;"><col style="width:23px;"><col style="width:23px;"><col style="width:23px;"><col style="width:23px;"><col style="width:23px;"><col style="width:23px;"><col style="width:23px;"><col style="width:23px;"><col style="width:23px;"><col style="width:23px;"><col style="width:23px;"><col style="width:89px;"><col style="width:43px;"><col style="width:43px;"><col style="width:43px;"><col style="width:43px;"><col style="width:43px;"><col style="width:43px;"><col style="width:43px;"></colgroup><tbody>
</tbody>
 
Upvote 0
Try

Col ACol R
All Games
DateGame 1Game 2Game 3 Game4 12345678
02/07/20191vs2 3vs4 5vs6 7vs8 21436587
2/14/20196vs8 5vs7 2vs4 1vs3 34127856
2/21/20195vs4 1vs8 7vs3 2vs6 86754231
2/28/20193vs6 7vs2 1vs5 8vs4 57681324
03/07/20197vs1 4vs6 3vs8 5vs2 75862413
3/14/20192vs3 8vs5 4vs1 6vs7 43218765
3/21/20194vs7 6vs1 8vs2 3vs5 68573142
3/28/20195vs6 7vs8 3vs4 1vs2 21436587
04/04/20192vs4 1vs3 5vs7 6vs8 34127856
04/11/20197vs3 2vs6 1vs8 5vs4 86754231
4/18/20191vs5 8vs4 7vs2 3vs6 57681324
4/25/20193vs8 5vs2 4vs6 7vs1 75862413
Formula in R4: =IF(INDEX($B4:$Q4,1,MATCH(R$3,$B4:$Q4,0)+1)="vs",INDEX($B4:$Q4,1,MATCH(R$3,$B4:$Q4,0)+2),INDEX($B4:$Q4,1,MATCH(R$3,$B4:$Q4,0)-2))

<colgroup><col><col><col><col><col><col><col><col><col><col span="3"><col><col span="3"><col><col span="8"></colgroup><tbody>
</tbody>
 
Upvote 0
James Lever said:
Try

Col ACol R
All Games
DateGame 1Game 2Game 3Game412345678
02/07/20191vs23vs45vs67vs821436587
2/14/20196vs85vs72vs41vs334127856
2/21/20195vs41vs87vs32vs686754231
2/28/20193vs67vs21vs58vs457681324
03/07/20197vs14vs63vs85vs275862413
3/14/20192vs38vs54vs16vs743218765
3/21/20194vs76vs18vs23vs568573142
3/28/20195vs67vs83vs41vs221436587
04/04/20192vs41vs35vs76vs834127856
04/11/20197vs32vs61vs85vs486754231
4/18/20191vs58vs47vs23vs657681324
4/25/20193vs85vs24vs67vs175862413
Formula in R4: =IF(INDEX($B4:$Q4,1,MATCH(R$3,$B4:$Q4,0)+1)="vs",INDEX($B4:$Q4,1,MATCH(R$3,$B4:$Q4,0)+2),INDEX($B4:$Q4,1,MATCH(R$3,$B4:$Q4,0)-2))

<tbody>
</tbody>
Click to expand...


Thank you both for the help. It is much appreciated.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
need data using right function
2
Replies
14
Views
298
sksanjeev786
S
JenniferMurphy
  • Question
Best way to populate a table with a macro
Replies
2
Views
202
JenniferMurphy
JenniferMurphy
C
  • Question
Making an easy to use Form and Report Data
Replies
0
Views
199
Chewwwy
C
A
  • Question
Sum of hours across two or more column cell range based on criteria
Replies
1
Views
161
aspring
A
R
  • Question
Code to copy then paste certain columns as values
Replies
4
Views
116
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,203,069
Messages
6,053,350
Members
444,655
Latest member
didr

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