Find closest to Target with a variable

[GamerNeelie]

I have a complicated formula that I need to expand on to filter out any results with with less then a set figure

Below is a cut down example of a larger sheet

Table in (E-G)
I know how to sort the files closest to the target (C2) and return the match for name and times

Table in (I-K)
I want to be able to sort the list while hitting the variable in (F2) and display results in J6 with anything less then then the value in (F2) being cut out

Current code I am using to sort the list starting in Cell (F6) - I use CTRL+SHIFT when entering this to make it work
=INDEX($B$6:$B$14, MATCH(SMALL(ABS(B$6:$B$14-$C$2), ROW(F1)), IF(COUNTIF($F$4:F4, B$6:$B$14)<COUNTIF(B$6:$B$14, B$6:$B$14), ABS(B$6:$B$14-$C$2), "A"), 0))

Hope I have explained it as best as I can.

First time on forum too.. So Hi :D

Thanks.

 

[GamerNeelie]

Also is there a way to stop duplicates if the values are exactly the same? EG John and Lisa

(Chances of two values being the same are very very small but just in case this does come up)

 

[GamerNeelie]

Any Ideas guys?

 

[Eric W]

Welcome to the MrExcel forum!

Consider:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2		Target	160		Files	4
3
4	Raw Data				Sorted without files				Sorted with Min files				Using array functions
5	Name	Result	Files		Name	Result	Times		Name	Result	Times		Name	Result	Times
6	Mark	158.2	8		Paul	158.4	6		Paul	158.4	6		Paul	158.4	6
7	Paul	158.4	6		Mark	158.2	8		Mark	158.2	8		Mark	158.2	8
8	John	165.8	2		Fred	156.2	9		Fred	156.2	9		Fred	156.2	9
9	Luke	169.4	9		John	165.8	2		Lisa	165.8	8		Lisa	165.8	8
10	Mike	178.2	12		Lisa	165.8	8		Jeff	152.5	6		Jeff	152.5	6
11	Lisa	165.8	8		Jeff	152.5	6		Luke	169.4	9		Luke	169.4	9
12	Katie	175.9	3		Luke	169.4	9		Mike	178.2	12		Mike	178.2	12
13	Fred	156.2	9		Katie	175.9	3
14	Jeff	152.5	6		Mike	178.2	12
15
Sheet16

Cell Formulas
Range	Formula
E6:E14	E6	=INDEX($A:$A,AGGREGATE(15,6,ROW($A$6:$A$14)/($B$6:$B$14=F6),COUNTIF($F$6:$F6,$F6)))
F6:F14	F6	=INDEX($B$6:$B$14, MATCH(SMALL(ABS(B$6:$B$14-$C$2), ROW(F1)), IF(COUNTIF($F$4:F4, B$6:$B$14)<COUNTIF(B$6:$B$14, B$6:$B$14), ABS(B$6:$B$14-$C$2), "A"), 0))
G6:G14	G6	=INDEX($C:$C,AGGREGATE(15,6,ROW($A$6:$A$14)/($B$6:$B$14=F6),COUNTIF($F$6:$F6,$F6)))
I6:I14	I6	=IF(J6="","",INDEX($A:$A,AGGREGATE(15,6,ROW($A$6:$A$14)/($B$6:$B$14=J6)/($C$6:$C$14>=$F$2),COUNTIF($J$6:$J6,$J6))))
J6:J14	J6	=IFERROR(INDEX($B$6:$B$14,MATCH(AGGREGATE(15,6,ABS($B$6:$B$14-$C$2)/($C$6:$C$14>=$F$2),ROWS($J$6:$J6)),IF($C$6:$C$14<$F$2,-1,ABS($B$6:$B$14-$C$2)),0)),"")
K6:K14	K6	=IF(J6="","",INDEX($C:$C,AGGREGATE(15,6,ROW($A$6:$A$14)/($B$6:$B$14=J6)/($C$6:$C$14>=$F$2),COUNTIF($J$6:$J6,$J6))))
M6:O12	M6	=LET(t,SORTBY(A6:C14,ABS(B6:B14-C2)),FILTER(t,INDEX(t,0,3)>=F2))
Press CTRL+SHIFT+ENTER to enter array formulas.
Dynamic array formulas.

The E and G formulas you can use with your existing formula to prevent the same name showing up when there's a tie. The I, J, K formulas should work for you to exclude the rows with not enough files.

And just to show how much easier it is with the new(ish) array formulas, how to do it with those in the M6 formula. Hope this helps!

 

[GamerNeelie]

Thank you Eric. Ill give it a try :D

 

[GamerNeelie]

What do the 15,6 numbers means in the part of the forumula "AGGREGATE(15,6,ROW"

Ill be using this in different cells and sheets around my workbook but unsure what they are for?

 

[Eric W]

AGGREGATE is a composite function that does several functions. 15 is the identifier for SMALL. The next parameter is a modifier of the function. 6 means "Ignore errors." So the formula basically works by creating a list of numbers, dividing them by a TRUE/FALSE condition, and if it's false, it ends up dividing by 0 causing an error, thereby removing it from consideration.

 

[GamerNeelie]

AGGREGATE is a composite function that does several functions. 15 is the identifier for SMALL. The next parameter is a modifier of the function. 6 means "Ignore errors." So the formula basically works by creating a list of numbers, dividing them by a TRUE/FALSE condition, and if it's false, it ends up dividing by 0 causing an error, thereby removing it from consideration.

Thank you so much for your help. Worked perfectly

 

[Eric W]

Happy to help! Thanks for the feedback.