How to find N similar items with 2 or more columns?

[Waimea]

Hi,

I want to find N similar items based on 2 or more columns. In the example below I am trying to find similar items based on 3 columns.

[TABLE="width: 508"]
<tbody>[TR]
[TD]City[/TD]
[TD]             Population[/TD]
[TD]     Number of pets[/TD]
[TD]       Number of traffic lights[/TD]
[/TR]
[TR]
[TD]City1[/TD]
[TD="align: right"]10561[/TD]
[TD="align: right"]466[/TD]
[TD="align: right"]121[/TD]
[/TR]
[TR]
[TD]City2[/TD]
[TD="align: right"]10444[/TD]
[TD="align: right"]498[/TD]
[TD="align: right"]101[/TD]
[/TR]
[TR]
[TD]City3[/TD]
[TD="align: right"]928[/TD]
[TD="align: right"]280[/TD]
[TD="align: right"]61[/TD]
[/TR]
[TR]
[TD]City4[/TD]
[TD="align: right"]13175[/TD]
[TD="align: right"]103[/TD]
[TD="align: right"]139[/TD]
[/TR]
[TR]
[TD]City5[/TD]
[TD="align: right"]4858[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]143[/TD]
[/TR]
[TR]
[TD]City6[/TD]
[TD="align: right"]13607[/TD]
[TD="align: right"]451[/TD]
[TD="align: right"]59[/TD]
[/TR]
[TR]
[TD]City7[/TD]
[TD="align: right"]1357[/TD]
[TD="align: right"]321[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD]City8[/TD]
[TD="align: right"]3116[/TD]
[TD="align: right"]249[/TD]
[TD="align: right"]123[/TD]
[/TR]
[TR]
[TD]City9[/TD]
[TD="align: right"]9330[/TD]
[TD="align: right"]450[/TD]
[TD="align: right"]79[/TD]
[/TR]
[TR]
[TD]City10[/TD]
[TD="align: right"]11523[/TD]
[TD="align: right"]441[/TD]
[TD="align: right"]129[/TD]
[/TR]
[TR]
[TD]City11[/TD]
[TD="align: right"]6475[/TD]
[TD="align: right"]431[/TD]
[TD="align: right"]128[/TD]
[/TR]
[TR]
[TD]City12[/TD]
[TD="align: right"]8793[/TD]
[TD="align: right"]414[/TD]
[TD="align: right"]119[/TD]
[/TR]
[TR]
[TD]City13[/TD]
[TD="align: right"]1133[/TD]
[TD="align: right"]493[/TD]
[TD="align: right"]113[/TD]
[/TR]
[TR]
[TD]City14[/TD]
[TD="align: right"]6390[/TD]
[TD="align: right"]216[/TD]
[TD="align: right"]74[/TD]
[/TR]
[TR]
[TD]City15[/TD]
[TD="align: right"]14740[/TD]
[TD="align: right"]239[/TD]
[TD="align: right"]96[/TD]
[/TR]
[TR]
[TD]City16[/TD]
[TD="align: right"]14906[/TD]
[TD="align: right"]393[/TD]
[TD="align: right"]128[/TD]
[/TR]
[TR]
[TD]City17[/TD]
[TD="align: right"]8281[/TD]
[TD="align: right"]201[/TD]
[TD="align: right"]76[/TD]
[/TR]
[TR]
[TD]City18[/TD]
[TD="align: right"]8301[/TD]
[TD="align: right"]227[/TD]
[TD="align: right"]96[/TD]
[/TR]
[TR]
[TD]City19[/TD]
[TD="align: right"]12784[/TD]
[TD="align: right"]233[/TD]
[TD="align: right"]53[/TD]
[/TR]
[TR]
[TD]City20[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]47[/TD]
[/TR]
</tbody>[/TABLE]

I would like to add more rows and more columns and also to determine the spann of which the items are similar.

For population = 2500, for number of pets = 100 and for number of traffic lights = 20.

If I select City1 similar items would be City2, City10.

 

[Fluff]

How about

Book1
	A	B	C	D	E	F	G	H
1	City	Population	Number of pets	Number of traffic lights
2	City1	10561	466	121		City	City1	City1
3	City2	10444	498	101		Population	2500	City2
4	City3	928	280	61		Number of pets	100	City10
5	City4	13175	103	139		Number of traffic lights	20	City12
6	City5	4858	45	143
7	City6	13607	451	59
8	City7	1357	321	80
9	City8	3116	249	123
10	City9	9330	450	79
11	City10	11523	441	129
12	City11	6475	431	128
13	City12	8793	414	119
14	City13	1133	493	113
15	City14	6390	216	74
16	City15	14740	239	96
17	City16	14906	393	128
18	City17	8281	201	76
19	City18	8301	227	96
20	City19	12784	233	53
21	City20	10000	150	47
Sheet4

Cell Formulas
Range	Formula
H2	=IFERROR(INDEX($A$2:$A$41,AGGREGATE(15,6,(ROW($A$2:$A$41)-ROW($A$2)+1)/((ABS($B$2:$B$41-INDEX($B$2:$B$41,MATCH($G$2,$A$2:$A$41,0)))<=$G$3)*(ABS($C$2:$C$41-INDEX($C$2:$C$41,MATCH($G$2,$A$2:$A$41,0)))<=$G$4)*(ABS($D$2:$D$41-INDEX($D$2:$D$41,MATCH($G$2,$A$2:$A$41,0)))<=$G$5)),ROWS($A$2:$A2))),"")

 

[Waimea]

Hi Fluff,

thank you very much for your reply!

This is working like in the earlier thread but this is way cooler and it is really clever use of formulas!

Thank you again for your help and for helping me to find N items!

 

[Fluff]

You're welcome & thanks for the feedback

 

[Waimea]

I just added some more columns and I wanted to say that your formula is working really well!

Thank you again!

 

[Fluff]

Glad to hear that & thanks for the info.