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]

Without knowing your layout, how about something like

Excel 2013/2016
	A	B	C	D	E
1	x	y
2	1	abc	0	def	def
3	0	def	0	pqr	pqr
4	5	ghi	0	stu	stu
5	7	jkl	1	abc	abc
6	9	mno	3	vwx	vwx
7	0	pqr	5	ghi	ghi
8	0	stu	7	jkl	jkl
9	3	vwx	9	mno	mno
Orders

Cell Formulas
Range	Formula
C2	=SMALL(A$2:A$9,ROWS(A$2:A2))
E2	=IFERROR(INDEX($B$2:$B$9,AGGREGATE(15,6,(ROW($B$2:$B$9)-ROW($B$2)+1)/($A$2:$A$9=C2),COUNTIF($C$2:C2,C2))),"")
D2	{=INDEX($B$2:$B$9,SMALL(IF($A$2:$A$9=C2,ROW($B$2:$B$9)-1),COUNTIF(C$2:C2,C2)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[AbsentStream]

Cheers Fluff!

I think this should work just fine. I'll be able to check it out with the data I'm using tomorrow but seems to do exactly what I need it to do in this example.

A1 for the speedy response also!

Cheers,
Absent.

 

[Fluff]

You're welcome & thanks for the feedback.
Let me know if there's any problems

 

[AbsentStream]

Hi Fluff, this works very well.

Just one slight problem, say we add another column with people's names as variable Z in your example above.

Is there a way of altering the worksheet formula so that it can group per name as well? Otherwise, using the above example, I'm left with some duplicates as it seems to reuse the same code it's already used for some of the numbers where they're the same as previous ones.

I have tried for example: =IFERROR(INDEX($B$2:$B$9,AGGREGATE(15,6,(ROW($B$2:$B$9)-ROW($B$2)+1)/($A$2:$A$9=C2)/($F$2::$F$9 = E2),COUNTIF($C$2:C2,C2))),"") (Assuming E2 is the name we want to look at and F1:F9 is the list of names) but this seems to just skip over values that have already been used and not input them into the cell.

 

[Fluff]

Can you please supply some sample data, there are add-ins available here to help you do that. https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[AbsentStream]

Please See below fluff (was not able to download any from that link).

So I want to group the 5 smallest figures for each name in this example. (Amalgamating Tom and Tom2 together to show the smallest 5 across both as 'TOM')

Then using them figures, I want it to pull the associated code from column B into cell G.

The worksheet formula you used above works to some extent but still seeing the dupe problem.

[TABLE="width: 448"]
<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 64"]Name[/TD]
[TD="class: xl66, width: 64"]Code[/TD]
[TD="class: xl66, width: 64"]Number[/TD]
[TD="class: xl66, width: 64"] [/TD]
[TD="class: xl66, width: 64"]NAME[/TD]
[TD="class: xl66, width: 64"]NUMBER[/TD]
[TD="class: xl66, width: 64"]CODE[/TD]
[/TR]
[TR]
[TD="class: xl63"]JOHN [/TD]
[TD="class: xl63"]AB[/TD]
[TD="class: xl63"]-10[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]JOHN[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl64"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]JOHN [/TD]
[TD="class: xl63"]CD[/TD]
[TD="class: xl63"]-2[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]JOHN[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl64"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]JOHN [/TD]
[TD="class: xl63"]EF[/TD]
[TD="class: xl63"]0[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]JOHN[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl64"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]JOHN [/TD]
[TD="class: xl63"]GH[/TD]
[TD="class: xl63"]0[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]JOHN[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl64"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]JOHN [/TD]
[TD="class: xl63"]IJ[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]JOHN[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl64"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]JOHN [/TD]
[TD="class: xl63"]KL[/TD]
[TD="class: xl63"]6[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl64"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]JOHN[/TD]
[TD="class: xl63"]MN[/TD]
[TD="class: xl63"]8[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]PAT[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl64"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]JOHN [/TD]
[TD="class: xl63"]OP[/TD]
[TD="class: xl63"]10[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]PAT[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl64"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]PAT[/TD]
[TD="class: xl63"]QR[/TD]
[TD="class: xl63"]-8[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]PAT[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl64"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]PAT[/TD]
[TD="class: xl63"]ST[/TD]
[TD="class: xl63"]-2[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]PAT[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl64"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]PAT[/TD]
[TD="class: xl63"]UV[/TD]
[TD="class: xl63"]0[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]PAT[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl64"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]PAT[/TD]
[TD="class: xl63"]WX[/TD]
[TD="class: xl63"]0[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl64"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]PAT[/TD]
[TD="class: xl63"]YZ[/TD]
[TD="class: xl63"]3[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]TOM[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl64"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]PAT[/TD]
[TD="class: xl63"]ABC[/TD]
[TD="class: xl63"]6[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]TOM[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl64"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]PAT[/TD]
[TD="class: xl63"]DEF[/TD]
[TD="class: xl63"]8[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]TOM[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl64"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]PAT[/TD]
[TD="class: xl63"]GHI[/TD]
[TD="class: xl63"]8[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]TOM[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl64"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]PAT[/TD]
[TD="class: xl63"]JKL[/TD]
[TD="class: xl63"]11[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]TOM[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl64"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]PAT[/TD]
[TD="class: xl63"]MNO[/TD]
[TD="class: xl63"]12[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl64"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]PAT[/TD]
[TD="class: xl63"]PQR[/TD]
[TD="class: xl63"]14[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl64"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]TOM[/TD]
[TD="class: xl63"]BLUE[/TD]
[TD="class: xl63"]-2[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]TOM[/TD]
[TD="class: xl63"]RED[/TD]
[TD="class: xl63"]0[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]TOM[/TD]
[TD="class: xl63"]YELLOW[/TD]
[TD="class: xl63"]4[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]TOM[/TD]
[TD="class: xl63"]PURPLE[/TD]
[TD="class: xl63"]6[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]TOM[/TD]
[TD="class: xl63"]BLACK[/TD]
[TD="class: xl63"]12[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]TOM[/TD]
[TD="class: xl63"]GREEN[/TD]
[TD="class: xl63"]11[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]TOM2[/TD]
[TD="class: xl63"]WHITE[/TD]
[TD="class: xl63"]-8[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]TOM2[/TD]
[TD="class: xl63"]SILVER[/TD]
[TD="class: xl63"]-4[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]TOM2[/TD]
[TD="class: xl63"]GOLD[/TD]
[TD="class: xl63"]0[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]TOM2[/TD]
[TD="class: xl63"]GREY[/TD]
[TD="class: xl63"]4[/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[TD="class: xl65"] [/TD]
[/TR]
</tbody>[/TABLE]

Again, any help is appreciated here.

Cheers,

Absent.

 

[AbsentStream]

Hey Fluff, I've replied with a raw data example from excel. Just waiting on Admin to approve it.

 

[Fluff]

Unfortunately I have bitten of more than I can chew with this.
Hopefully one of the Formula experts will step in & help.

 

[AbsentStream]

Not to worry.

This has quite a few of us stumped also. Was hoping I could somehow use a count formula to tell us what row each number is coming from and then use that as a reference for which code is assigned to each number, distinguishing between the dupes.

May just have to revert to using a mixture of VBA and excel formulae afterwards.