Excel Matching Function for distinct values related to particular rows/columns.

[AbsentStream]

Hi Guys

Having a problem with the Index matching function on excel.

I'm using a SMALL IF function to pull the smallest 10 figures from one sheet to another and then using index matching to take these figures and pull a particular code associated to that figure from the sheet (The code and the figure are in the same row in the sheet FYI). This works for where we have values which don't have duplicates in the sheet fine. The problem that's popping up is that some values are the same but have different codes and I don't know how to distinguish which code Is the one we're looking at for that particular figure using an excel function.

For example, '0' is a figure which pops up a lot but the index matching function I'm using only returns one code for all '0' values from the sheet, rather than the one associated with that code.

Is there a way of making the index function or the small if function pull in the row/column to which it is pulling that figure from so we can match it to a code? Or is there a better way to go about this?

Appreciate Any Detailed Help With This,

Absent.

 

[Fluff]

If your happy to change the layout, you could use

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Name	Code	Number		NAME	NUMBER	CODE
2	JOHN	AB	-10		JOHN	-10	AB		PAT	-8	QR		TOM	-2	BLUE
3	JOHN	CD	-2		JOHN	-6	KL		PAT	-6	ABC		TOM	0	RED
4	JOHN	EF	0		JOHN	-2	CD		PAT	-2	ST		TOM	4	YELLOW
5	JOHN	GH	0		JOHN	0	GH		PAT	0	WX		TOM	6	PURPLE
6	JOHN	IJ	2		JOHN	0	EF		PAT	0	UV		TOM	11	GREEN
7	JOHN	KL	-6
8	JOHN	MN	8
9	JOHN	OP	10
10	PAT	QR	-8
11	PAT	ST	-2
12	PAT	UV	0
13	PAT	WX	0
14	PAT	YZ	3
15	PAT	ABC	-6
16	PAT	DEF	8
17	PAT	GHI	8
18	PAT	JKL	11
19	PAT	MNO	12
20	PAT	PQR	14
21	TOM	BLUE	-2
22	TOM	RED	0
23	TOM	YELLOW	4
24	TOM	PURPLE	6
25	TOM	BLACK	12
26	TOM	GREEN	11
27	TOM2	WHITE	-8
28	TOM2	SILVER	-4
29	TOM2	GOLD	0
30	TOM2	GREY	4
Sheet2

Cell Formulas
Range	Formula
G2	=INDEX($B$2:$B$30,AGGREGATE(14,6,(ROW($B$2:$B$30)-ROW($B$2)+1)/($A$2:$A$30=E2)*($C$2:$C$30=F2),COUNTIF($F$2:F2,F2)))
K2	=INDEX($B$2:$B$30,AGGREGATE(14,6,(ROW($B$2:$B$30)-ROW($B$2)+1)/($A$2:$A$30=I2)*($C$2:$C$30=J2),COUNTIF($J$2:J2,J2)))
O2	=INDEX($B$2:$B$30,AGGREGATE(14,6,(ROW($B$2:$B$30)-ROW($B$2)+1)/($A$2:$A$30=M2)*($C$2:$C$30=N2),COUNTIF($F$2:N2,N2)))
F2	{=SMALL(IF($A$2:$A$30=E2,$C$2:$C$30),ROWS($1:1))}
J2	{=SMALL(IF($A$2:$A$30=I2,$C$2:$C$30),ROWS($1:1))}
N2	{=SMALL(IF($A$2:$A$30=M2,$C$2:$C$30),ROWS($1:1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Although I cannot manage the TOM or TOM2