Need Help with Index Function

kmprice710

New Member
Joined
Jan 8, 2014
Messages
32
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?
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,219
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,109,020
Messages
5,526,296
Members
409,694
Latest member
bastos21

This Week's Hot Topics

Top