How to find similar numbers?

[excelos]

Hello

I have a two lists of numbers. The numbers are from 0.0001 up to 1,999,999,999.

I want to find the pairs of numbers that are quite similar, i.e.within +/-5%.

How can I do that?

I was thinking to round them up and find exact matches, but they have large difference.

What do you suggest?

 

[shg]

Sort them:

	A​	B​	C​
1​	Num​	​	​
2​	0.10​
3​	0.13​	30.0%​	B3: =A3/A2-1
4​	0.16​	23.1%​
5​	0.17​	6.3%​
6​	0.24​	41.2%​
7​	0.25​	4.2%​
8​	0.32​	28.0%​
9​	0.34​	6.3%​
10​	0.35​	2.9%​
11​	0.38​	8.6%​
12​	0.38​	0.0%​
13​	0.41​	7.9%​
14​	0.55​	34.1%​
15​	0.59​	7.3%​
16​	0.61​	3.4%​
17​	0.62​	1.6%​
18​	0.63​	1.6%​
19​	0.64​	1.6%​
20​	0.73​	14.1%​
21​	0.78​	6.8%​
22​	0.83​	6.4%​
23​	0.92​	10.8%​
24​	1.04​	13.0%​

 

[shg]

Rereading that, sort both lists, then

	A​	B​	C​	D​	E​	F​	G​	H​
1​	List1​	From List2​	Diff​	​	List2​	From List 1​	Diff​	​
2​	0.11​	#N/A​	#N/A​	B2: =INDEX($E$2:$E$26, MATCH(A2, $E$2:$E$26))	0.88​	0.36​	59%​	F2: =INDEX($A$2:$A$26, MATCH(E2, $A$2:$A$26))
3​	0.15​	#N/A​	#N/A​	C2: =1 - B2/A2	0.89​	0.36​	60%​	G2: =1 - F2/E2
4​	0.36​	#N/A​	#N/A​		1.31​	1.07​	18%​
5​	1.07​	0.89​	17%​		1.43​	1.07​	25%​
6​	1.98​	1.64​	17%​		1.64​	1.07​	35%​
7​	3.15​	3.00​	5%​		3.00​	1.98​	34%​
8​	3.88​	3.00​	23%​		5.80​	5.01​	14%​
9​	3.89​	3.00​	23%​		5.87​	5.01​	15%​
10​	4.09​	3.00​	27%​		5.95​	5.01​	16%​
11​	5.01​	3.00​	40%​		6.18​	5.01​	19%​
12​	6.60​	6.23​	6%​		6.23​	5.01​	20%​
13​	6.67​	6.23​	7%​		6.88​	6.71​	2%​
14​	6.71​	6.23​	7%​		7.68​	7.58​	1%​
15​	7.06​	6.88​	3%​		10.68​	10.59​	1%​
16​	7.18​	6.88​	4%​		10.78​	10.59​	2%​
17​	7.37​	6.88​	7%​		11.00​	10.83​	2%​
18​	7.58​	6.88​	9%​		12.82​	12.41​	3%​
19​	8.28​	7.68​	7%​		13.61​	12.41​	9%​
20​	8.45​	7.68​	9%​		13.84​	12.41​	10%​
21​	10.59​	7.68​	27%​		13.90​	12.41​	11%​
22​	10.83​	10.78​	0%​		15.06​	12.41​	18%​
23​	11.06​	11.00​	1%​		15.30​	12.41​	19%​
24​	11.13​	11.00​	1%​		15.33​	12.41​	19%​
25​	11.59​	11.00​	5%​		15.93​	12.41​	22%​
26​	12.41​	11.00​	11%​		16.22​	12.41​	23%​

 

[excelos]

Unfortunately the two lists do not have the same size/length.


I will need a VLOOKUP method to search and find the closest matches, within a tolerance of eg 5%

 

[shg]

They need not have the same size.