Finding the Nth Smallest Position

[ModelMad]

Hello,

Thanks for reading. For a model i am working on i need to move activity from one building to the nth closest match which matches a specified criteria. I think i need to use the SMALL function to return the position of the smallest distance which matches my criteria, i can then offset this to the Unique ID of that site which is positioned above. Note i can't use the MATCH function as sometimes the postcodes are the same but the buildings are different (and do not match my criteria) and Match will return the first match only.

My formula below is consistently returning criteria matches but not the nth closest:

=OFFSET('Postcode Distance Matrix'!$F$7,-3,SMALL(IF(('Postcode Distance Matrix'!$G$3:$BR$3='Space Required Pivot '!$G10)*('Postcode Distance Matrix'!$G12:$BR12), COLUMN('Postcode Distance Matrix'!$G$3:$BR$3)-COLUMN('Postcode Distance Matrix'!$G12)+1), K$3))

Postcode Distance Matrix G3:BR3 - matching criteria
Space Required Pivot G - Matching criteria
Postcode Distance Matrix G12:BR12 - contains distance between buildings

Any idea how i can amend my formula to consistently return the nth smallest match??

 

[Fluff]

How about

=XLOOKUP(SMALL(IF($B$2:$F$2=$J4,B4:F4), K2),B4:F4,B3:F3)

 

[ModelMad]

How about

=XLOOKUP(SMALL(IF($B$2:$F$2=$J4,B4:F4), K2),B4:F4,B3:F3)

Thanks for this suggestion, it works on the isolated example but in my full dataset it still has issues with returning the wrong site code where two buildings are the same distance from the origin. I think it is still finding the first match which matches the specifications.

 

[Fluff]

Would you always want the last match?

 

[ModelMad]

Would you always want the last match?

No, not necessarily. The brief is I want to identify the closest building which matches my criteria, there are some buildings which sit within the same postcode and are therefore the same distance from my origin property but have different criteria. if i use MATCH+SMALL or LOOKUP+SMALL the formula will return the first property it finds which is the same distance from the origin property as the one which matches the criteria, this isn't always the same property.

 

[Fluff]

If you have two buildings which are the same distance, how is a formula going to know which you want?

 

[ModelMad]

If you have two buildings which are the same distance, how is a formula going to know which you want?

Because they have different criteria

 

[felixstraube]

how about this?: formula in L4

Book5
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2	Criteria	CA	CB	CD	CA	CF					1
3	Site Code	AA	AB	AC	AD	AF			Site Code	Criteria	Full Formula	New formula	Expected result	Position Formula	Position Expected Result	Smallest matching critera result	Smallest Expected
4	BA	0.1	0.3	0.2	0.05	0.9			BA	CA	AA	AD	AD	1	4	0.05	0.05
5	BA	0.4	0	0.6	1.3	5.4			BA	CD
6	BB	0.8	1.2	0	2.2	3.3			BB
7	BC	0.9	4.5	0.1	0	0.4			BC
8	BD	1.1	2.2	3.5	5.5	0			BD
9
Sheet1

Cell Formulas
Range	Formula
K4	K4	=OFFSET(A3,0,SMALL(IF(($B$2:$F$2=$J4)*(B4:F4), COLUMN($B4:$F4)-COLUMN($B4)+1), K2))
L4	L4	=INDEX( $B$3:$F$3,1,MAX(ROW($B$4:$F$8)*($B$4:$F$8*($A$4:$A$8=I4)*($B$2:$F$2=J4)=SMALL(FILTER(FILTER($B$4:$F$8, $A$4:$A$8=I4), $B$2:$F$2=J4), K2))))
N4	N4	=SMALL(IF(($B$2:$F$2=$J4)*(B4:F4), COLUMN($B4:$F4)-COLUMN($B4)+1), K2)
P4	P4	=SMALL(IF($B$2:$F$2=J4,B4:F4),K2)
A4:A8	A4	=I4

 

[Fluff]

Ok, how about

=INDEX(SORT(FILTER(VSTACK($B$3:$F$3,B4:F4),$B$2:$F$2=J4),2,,1),1,K$2)

 

[felixstraube]

sorry deleted. Wont work either.

 

[felixstraube]

Ok now it is working properly... I think...
Let me know

SiteCodes.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2	Criteria	CA	CB	CD	CA	CF					1
3	Site Code	AA	AB	AC	AD	AF			Site Code	Criteria	Full Formula
4	BA	0.1	0.3	0.2	0.05	0.9			BA	CA	AD
5	BA	0.4	0	0.6	1.3	5.4			BA	CD	AC
6	BB	0.8	1.2	0	2.2	3.3			BB	CA	AA
7	BC	0.9	4.5	0.1	0	0.4			BC	CF	AF
8	BD	1.1	2.2	3.5	5.5	0			BD	CB	AB
Sheet1

Cell Formulas
Range	Formula
A4:A8	A4	=I4
K4:K8	K4	=INDEX($B$3:$F$3,,MAX(IFERROR((COLUMN($B$2:$F$2)-COLUMN($A$2))*($B$4:$F$8*IF($A$4:$A$8<>I4,"",1)*IF($B$2:$F$2<>J4,"",1)=SMALL(FILTER(FILTER($B$4:$F$8,$A$4:$A$8=I4),$B$2:$F$2=J4),$K$2)),0)))