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??

 

[awoohaw]

welcome to the Mr. Excel forum. The forum has a tool that allows you to share a mini portion of worksheets, called xl2bb add in, the link is below.
Please use this to share the data for your scenario. If you cannot use this, then please post a table of your data.

Expected results are always very helpful as well.

When not providing sample data (or posting an image) you're asking the forum to completely recreate (and guess in it too) your scenario. That can lead to errors and typos. It also means the forum members trying to solve your question will take more time in setting things up than a copy and paste.

Thanks in advance.

 

[felixstraube]

You would need to share the content of the sheets "Required PC", "Available PC" and "Space Available Pivot" also.

And one other thing: You seem to have circular references in your sheet.

 

[ModelMad]

Unfortunately there is too much sensitive info to post anything. If anyone has any tips on making returning the Position of the nth smallest number within a row which matches a criteria i am able to translate it into my formula. I just don't understand why the mechanics of what i have input are not returning the results i expect. e.g.

=OFFSET('Postcode Distance Matrix'!$F$7,-3,SMALL(IF(('Postcode Distance Matrix'!$G17:$BR17)*('Postcode Distance Matrix'!$G$3:$BR$3='Space Required Pivot '!$G15), COLUMN('Postcode Distance Matrix'!$G$3:$BR$3)-COLUMN('Postcode Distance Matrix'!$G17)+1), K$3)) returns the code of a site which is 9 miles away

=SMALL(IF(('Postcode Distance Matrix'!$G17:$BR17)*('Postcode Distance Matrix'!$G$3:$BR$3='Space Required Pivot '!$G15), COLUMN('Postcode Distance Matrix'!$G$3:$BR$3)-COLUMN('Postcode Distance Matrix'!$G17)+1), L$3) returns position 34 which is the same as above.

However:

=SMALL(IF(('Postcode Distance Matrix'!$G$3:$BR$3='Space Required Pivot '!$G15),'Postcode Distance Matrix'!$G17:$BR17), M$3) returns a 0 confirming my manual check that there is a suitable building in the same postcode.

Any comments would be very welcome

 

[felixstraube]

How about something like this:
In column E list of filtered results (just to show them), and in cell F2 the complete formula with SMALL, getting the 3rd smallest value.

Book4
	A	B	C	D	E	F
1	Criteria 1	Cirteria 2	Value to return		Filtered by criteria only	third smallest
2	a	1	1		1	11
3	a	1	2		2
4	a	2	3		11
5	a	2	4		12
6	a	2	5
7	b	1	6
8	b	1	7
9	b	2	8
10	b	2	9
11	b	2	10
12	a	1	11
13	a	1	12
14	a	2	13
15	a	2	14
16	a	2	15
17	b	1	16
18	b	1	17
19	b	2	18
20	b	2	19
21	b	2	20
22
Sheet1

Cell Formulas
Range	Formula
E2:E5	E2	=FILTER(C2:C21,(A2:A21="a")*(B2:B21=1))
F2	F2	=SMALL(FILTER(C2:C21,(A2:A21="a")*(B2:B21=1)), 3)
Dynamic array formulas.

 

[ModelMad]

How about something like this:
In column E list of filtered results (just to show them), and in cell F2 the complete formula with SMALL, getting the 3rd smallest value.

Book4
	A	B	C	D	E	F
1	Criteria 1	Cirteria 2	Value to return		Filtered by criteria only	third smallest
2	a	1	1		1	11
3	a	1	2		2
4	a	2	3		11
5	a	2	4		12
6	a	2	5
7	b	1	6
8	b	1	7
9	b	2	8
10	b	2	9
11	b	2	10
12	a	1	11
13	a	1	12
14	a	2	13
15	a	2	14
16	a	2	15
17	b	1	16
18	b	1	17
19	b	2	18
20	b	2	19
21	b	2	20
22
Sheet1

Cell Formulas
Range	Formula
E2:E5	E2	=FILTER(C2:C21,(A2:A21="a")*(B2:B21=1))
F2	F2	=SMALL(FILTER(C2:C21,(A2:A21="a")*(B2:B21=1)), 3)
Dynamic array formulas.

Thanks for the suggestion - i need to return the position of the value as opposed to just identifying it. the position is then combined with an OFFSET formula to provide the site's unique ID

 

[felixstraube]

Are the values unique? If so, how about?:

Book4
	A	B	C	D	E	F	G
1	Criteria 1	Cirteria 2	Value to return		Filtered by criteria only	third smallest	Index or postion
2	a	1	10		10	110	11
3	a	1	20		20
4	a	2	30		110
5	a	2	40		120
6	a	2	50
7	b	1	60
8	b	1	70
9	b	2	80
10	b	2	90
11	b	2	100
12	a	1	110
13	a	1	120
14	a	2	130
15	a	2	140
16	a	2	150
17	b	1	160
18	b	1	170
19	b	2	180
20	b	2	190
21	b	2	200
22
Sheet1

Cell Formulas
Range	Formula
E2:E5	E2	=FILTER(C2:C21,(A2:A21="a")*(B2:B21=1))
F2	F2	=SMALL(FILTER(C2:C21,(A2:A21="a")*(B2:B21=1)), 3)
G2	G2	=MATCH(SMALL(FILTER(C2:C21,(A2:A21="a")*(B2:B21=1)), 3), C2:C21, 0)
Dynamic array formulas.

 

[ModelMad]

book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2	Criteria	CA	CB	CD	CA	CF					1
3	Site Code	AA	AB	AC	AD	AF			Site Code	Criteria	Full Formula	Expected result	Position Formula	Position Expected Result	Smallest matching critera result	Smallest Expected
4	BA	0.10	0.30	0.20	0.05	0.90			BA	CA	AA	AD	1	4	0.05	0.05
5	BA	0.40	0.00	0.60	1.30	5.40			BA	CD
6	BB	0.80	1.20	0.00	2.20	3.30			BB
7	BC	0.90	4.50	0.10	0.00	0.40			BC
8	BD	1.10	2.20	3.50	5.50	0.00			BD
9
10
11
12
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))
M4	M4	=SMALL(IF(($B$2:$F$2=$J4)*(B4:F4), COLUMN($B4:$F4)-COLUMN($B4)+1), K2)
O4	O4	=SMALL(IF($B$2:$F$2=J4,B4:F4),K2)
A4:A8	A4	=I4

In the attached you will see my formula is failing to return the position of the lowest value with matching critera. what am i doing wrong??

 

[awoohaw]

deleted

 

[ModelMad]

Are the values unique? If so, how about?:

Book4
	A	B	C	D	E	F	G
1	Criteria 1	Cirteria 2	Value to return		Filtered by criteria only	third smallest	Index or postion
2	a	1	10		10	110	11
3	a	1	20		20
4	a	2	30		110
5	a	2	40		120
6	a	2	50
7	b	1	60
8	b	1	70
9	b	2	80
10	b	2	90
11	b	2	100
12	a	1	110
13	a	1	120
14	a	2	130
15	a	2	140
16	a	2	150
17	b	1	160
18	b	1	170
19	b	2	180
20	b	2	190
21	b	2	200
22
Sheet1

Cell Formulas
Range	Formula
E2:E5	E2	=FILTER(C2:C21,(A2:A21="a")*(B2:B21=1))
F2	F2	=SMALL(FILTER(C2:C21,(A2:A21="a")*(B2:B21=1)), 3)
G2	G2	=MATCH(SMALL(FILTER(C2:C21,(A2:A21="a")*(B2:B21=1)), 3), C2:C21, 0)
Dynamic array formulas.

Really appreciate the effort to try and recreate my issue, sincere thanks.

I have put together a quick recreation of the problem now which hopefully demonstrates it more effectively than my incoherent rambling and unoptimised formulas!