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

#### senechia

##### New Member
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

 Date Game 1 Game 2 Game 3 Game4 1 2 3 4 5 6 7 8 2/7/2019 1 vs 2 3 vs 4 5 vs 6 7 vs 8 2/14/2019 6 vs 8 5 vs 7 2 vs 4 1 vs 3 2/21/2019 5 vs 4 1 vs 8 7 vs 3 2 vs 6 2/28/2019 3 vs 6 7 vs 2 1 vs 5 8 vs 4 3/7/2019 7 vs 1 4 vs 6 3 vs 8 5 vs 2 3/14/2019 2 vs 3 8 vs 5 4 vs 1 6 vs 7 How do I return the values from the table on the left to the table above? 3/21/2019 4 vs 7 6 vs 1 8 vs 2 3 vs 5 3/28/2019 5 vs 6 7 vs 8 3 vs 4 1 vs 2 4/4/2019 2 vs 4 1 vs 3 5 vs 7 6 vs 8 4/11/2019 7 vs 3 2 vs 6 1 vs 8 5 vs 4 Example: 7 3 4/18/2019 1 vs 5 8 vs 4 7 vs 2 3 vs 6 4/25/2019 3 vs 8 5 vs 2 4 vs 6 7 vs 1

<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
​Put the game number in cell P1

Hoja1

 A B C D E F G H I J K L M 1 2 Date Game 1 Game 2 Game 3 Game4 3 02/07/2019 1 vs 2 3 vs 4 5 vs 6 7 vs 8 4 2/14/2019 6 vs 8 5 vs 7 2 vs 4 1 vs 3 5 2/21/2019 5 vs 4 1 vs 8 7 vs 3 2 vs 6 6 2/28/2019 3 vs 6 7 vs 2 1 vs 5 8 vs 4 7 03/07/2019 7 vs 1 4 vs 6 3 vs 8 5 vs 2 8 3/14/2019 2 vs 3 8 vs 5 4 vs 1 6 vs 7 9 3/21/2019 4 vs 7 6 vs 1 8 vs 2 3 vs 5 10 3/28/2019 5 vs 6 7 vs 8 3 vs 4 1 vs 2 11 04/04/2019 2 vs 4 1 vs 3 5 vs 7 6 vs 8 12 04/11/2019 7 vs 3 2 vs 6 1 vs 8 5 vs 4 13 4/18/2019 1 vs 5 8 vs 4 7 vs 2 3 vs 6 14 4/25/2019 3 vs 8 5 vs 2 4 vs 6 7 vs 1

<tbody>
</tbody>

Hoja1

 O P Q R S T U V 1 Game 1 2 1 2 3 4 5 6 7 8 3 2 1 4 8 6 5 5 4 6 6 3 7 7 1 8 3 2 9 7 4 10 6 5 11 4 2 12 7 3 13 5 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>

 Cell Formula 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>

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 A Col Q Game 1 Date Game 1 Game 2 Game 3 Game4 1 2 3 4 5 6 7 8 02/07/2019 1 vs 2 3 vs 4 5 vs 6 7 vs 8 2 1 2/14/2019 6 vs 8 5 vs 7 2 vs 4 1 vs 3 8 6 2/21/2019 5 vs 4 1 vs 8 7 vs 3 2 vs 6 5 4 2/28/2019 3 vs 6 7 vs 2 1 vs 5 8 vs 4 6 3 03/07/2019 7 vs 1 4 vs 6 3 vs 8 5 vs 2 7 1 3/14/2019 2 vs 3 8 vs 5 4 vs 1 6 vs 7 3 2 3/21/2019 4 vs 7 6 vs 1 8 vs 2 3 vs 5 7 4 3/28/2019 5 vs 6 7 vs 8 3 vs 4 1 vs 2 6 5 04/04/2019 2 vs 4 1 vs 3 5 vs 7 6 vs 8 4 2 04/11/2019 7 vs 3 2 vs 6 1 vs 8 5 vs 4 7 3 4/18/2019 1 vs 5 8 vs 4 7 vs 2 3 vs 6 5 1 4/25/2019 3 vs 8 5 vs 2 4 vs 6 7 vs 1 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>

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

Example would be for 2/7/19 there would be 8 teams playing and the formulas in that row need to be completed

I will check it

Last edited:
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

 A B C D E F G H I J K L M N O P Q R S T U V 1 2 Date Game 1 Game 2 Game 3 Game4 1 2 3 4 5 6 7 8 3 02/07/2019 1 vs 2 3 vs 4 5 vs 6 7 vs 8 2 1 4 3 6 5 8 7 4 2/14/2019 6 vs 8 5 vs 7 2 vs 4 1 vs 3 3 4 1 2 7 8 5 6 5 2/21/2019 5 vs 4 1 vs 8 7 vs 3 2 vs 6 8 6 7 5 4 2 3 1 6 2/28/2019 3 vs 6 7 vs 2 1 vs 5 8 vs 4 5 7 6 8 1 3 2 4 7 03/07/2019 7 vs 1 4 vs 6 3 vs 8 5 vs 2 7 5 8 6 2 4 1 3 8 3/14/2019 2 vs 3 8 vs 5 4 vs 1 6 vs 7 4 3 2 1 8 7 6 5 9 3/21/2019 4 vs 7 6 vs 1 8 vs 2 3 vs 5 6 8 5 7 3 1 4 2 10 3/28/2019 5 vs 6 7 vs 8 3 vs 4 1 vs 2 2 1 4 3 6 5 8 7 11 04/04/2019 2 vs 4 1 vs 3 5 vs 7 6 vs 8 3 4 1 2 7 8 5 6 12 04/11/2019 7 vs 3 2 vs 6 1 vs 8 5 vs 4 8 6 7 5 4 2 3 1 13 4/18/2019 1 vs 5 8 vs 4 7 vs 2 3 vs 6 5 7 6 8 1 3 2 4 14 4/25/2019 3 vs 8 5 vs 2 4 vs 6 7 vs 1 7 5 8 6 2 4 1 3

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

Try

 Col A Col R All Games Date Game 1 Game 2 Game 3 Game4 1 2 3 4 5 6 7 8 02/07/2019 1 vs 2 3 vs 4 5 vs 6 7 vs 8 2 1 4 3 6 5 8 7 2/14/2019 6 vs 8 5 vs 7 2 vs 4 1 vs 3 3 4 1 2 7 8 5 6 2/21/2019 5 vs 4 1 vs 8 7 vs 3 2 vs 6 8 6 7 5 4 2 3 1 2/28/2019 3 vs 6 7 vs 2 1 vs 5 8 vs 4 5 7 6 8 1 3 2 4 03/07/2019 7 vs 1 4 vs 6 3 vs 8 5 vs 2 7 5 8 6 2 4 1 3 3/14/2019 2 vs 3 8 vs 5 4 vs 1 6 vs 7 4 3 2 1 8 7 6 5 3/21/2019 4 vs 7 6 vs 1 8 vs 2 3 vs 5 6 8 5 7 3 1 4 2 3/28/2019 5 vs 6 7 vs 8 3 vs 4 1 vs 2 2 1 4 3 6 5 8 7 04/04/2019 2 vs 4 1 vs 3 5 vs 7 6 vs 8 3 4 1 2 7 8 5 6 04/11/2019 7 vs 3 2 vs 6 1 vs 8 5 vs 4 8 6 7 5 4 2 3 1 4/18/2019 1 vs 5 8 vs 4 7 vs 2 3 vs 6 5 7 6 8 1 3 2 4 4/25/2019 3 vs 8 5 vs 2 4 vs 6 7 vs 1 7 5 8 6 2 4 1 3 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>

Try

 Col A Col R All Games Date Game 1 Game 2 Game 3 Game4 1 2 3 4 5 6 7 8 02/07/2019 1 vs 2 3 vs 4 5 vs 6 7 vs 8 2 1 4 3 6 5 8 7 2/14/2019 6 vs 8 5 vs 7 2 vs 4 1 vs 3 3 4 1 2 7 8 5 6 2/21/2019 5 vs 4 1 vs 8 7 vs 3 2 vs 6 8 6 7 5 4 2 3 1 2/28/2019 3 vs 6 7 vs 2 1 vs 5 8 vs 4 5 7 6 8 1 3 2 4 03/07/2019 7 vs 1 4 vs 6 3 vs 8 5 vs 2 7 5 8 6 2 4 1 3 3/14/2019 2 vs 3 8 vs 5 4 vs 1 6 vs 7 4 3 2 1 8 7 6 5 3/21/2019 4 vs 7 6 vs 1 8 vs 2 3 vs 5 6 8 5 7 3 1 4 2 3/28/2019 5 vs 6 7 vs 8 3 vs 4 1 vs 2 2 1 4 3 6 5 8 7 04/04/2019 2 vs 4 1 vs 3 5 vs 7 6 vs 8 3 4 1 2 7 8 5 6 04/11/2019 7 vs 3 2 vs 6 1 vs 8 5 vs 4 8 6 7 5 4 2 3 1 4/18/2019 1 vs 5 8 vs 4 7 vs 2 3 vs 6 5 7 6 8 1 3 2 4 4/25/2019 3 vs 8 5 vs 2 4 vs 6 7 vs 1 7 5 8 6 2 4 1 3 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>

Thank you both for the help. It is much appreciated.

Replies
14
Views
298
Replies
2
Views
202
Replies
0
Views
199
Replies
1
Views
161
Replies
4
Views
116

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

### 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?

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