INDEX/MATCH - Return All matches

[chrizzle]

Hi, I'm new here and this post would most likely return the thread but.. I want to ask..

I have

NAME	SUB	YR
name1	sub1	31
name2	sub2	31
name3	sub1	31
name4	sub2	31
name5	sub3	31

<colgroup><col><col><col></colgroup><tbody>
</tbody>

As my data..
I'm using:
{=INDEX($D$2:$D$24,MATCH($B$2&$C$2,$E$2:$E$24&$F$2:$F$24,0))}
and that would result into have the correct names with the corresponding sub and yr. however, it will duplicate on all the succeeding cells.

Any formula that I could use so that the results won't repeat?

 

[chullan88]

In the lookup value argument for MATCH,make it relative reference ( remove dollar sign for B2 and C2)

 

[Macropod]

See, for example:
formula to return all matches from a list | Windows Secrets Lounge
Excel Formula Issues - Tech Support Forum

 

[Macropod]

In the lookup value argument for MATCH,make it relative reference ( remove dollar sign for B2 and C2)

Merely doing that won't return the second and subsequent matches...