Return different values using INDEX AND MATCH

[rdna123]

This is the current formula I am using:

=INDEX(B:B,MATCH($B9,C:C,0))

However, if there are two of the same number in B:B it only returns the first value of C that shows up even if there are multiple responses.
How do I get it to show the first value in one row and the second value in another?
Row 5 should show a different name/initials

Thanks!

 

[Joe4]

Welcome to the Board!

Have a look at Excel's new FILTER function (available in 365), which allows you to return multiple matches instead of just the first one.
See: FILTER function - Microsoft Support

 

[rdna123]

When I tried to use the formula it returned a SPILL error code for this

 

[AhoyNC]

See if this link helps on spill error. It's probably because you are using a whole column reference.

 

[Peter_SSs]

This is the current formula I am using:

=INDEX(B:B,MATCH($B9,C:C,0))

Is that really the formula you are using? We don't know exactly what your data looks like** but that formula looks unlikely to me.
See if you could adapt something like this.

** I suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

25 03 19.xlsm
	A	B	C	D	E	F
1		Associate	Value		Top 5	Value
2		QW	172.2		JP	179.4
3		US	171.3		LW	177.7
4		SX	176.4		BC	177.7
5		LW	177.7		GK	177.7
6		DL	177.6		DL	177.6
7		BC	177.7
8		HY	174.2
9		JP	179.4
10		GK	177.7
11		NC	174.4
12		JK	177.5
13		DT	175.1
14		BV	170.7
15
Top 5

Cell Formulas
Range	Formula
E2:F6	E2	=TAKE(SORT(B2:C14,2,-1),5)
Dynamic array formulas.