Need Help with Index Function

kmprice710

Board Regular
Joined
Jan 8, 2014
Messages
84
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Date
Player
Player
Code
4/1/1959
Chris
Tom
123
12/5/1962
Dan
Boyd
456
5/3/1961
Chris
Peter
678
10/4/1960Tom
Boyd
789

<tbody>
</tbody>

Date
Player
Code
4/1/1959
Tom
???
5/3/1961
Chris
???
10/4/1960
Boyd
???

<tbody>
</tbody>

Let's say I have a list of chess contests. I have the date and the two players and a game ID code.

Now below that I have a date and one of the players but I don't know the ID code.

How do I index the above table so that it will output the code if the date matches and one of the players is in that row?
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Try:

ABCDEFGH
1DatePlayerPlayerCodeDatePlayerCode
24/1/1959ChrisTom1234/1/1959Tom123
312/5/1962DanBoyd4565/3/1961Chris678
45/3/1961ChrisPeter67810/4/1960Boyd789
510/4/1960TomBoyd789

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet17

Array Formulas
CellFormula
H2{=INDEX($D$2:$D$5,SMALL(IF($A$2:$A$5=F2,IF(($B$2:$B$5=G2)+($C$2:$C$5=G2),ROW($A$2:$A$5)-ROW($A$2)+1)),1))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

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