Match 2 sets of data and return only if greater or less than 30 difference

[TheLos]

I have 2 sets of unmatched data, I need to match them based on their ID and then return any that are greater or less then 30 difference in "LEN".
Please help!

GIS_IDGIS_STGIS_LENRM_IDRM_STRM_LEN88OWL FEATHER CT276.940638988OWL FEATHER CT31289TALON REACH CT381.681697889TALON REACH CT37392EAGLE WIND DR262.032984890PACKARD DR65593RUNNING WOLF WY485.258064891INDUSTRIAL AV36694PRAIRIE WOODS WY504.866214492EAGLE WIND DR25895RUNNING WOLF CT273.974147893RUNNING WOLF WY47696STEAMBOAT LN214.347712594PRAIRIE WOODS WY53497EAGLE WIND DR262.034008295RUNNING WOLF CT31598BLUE OAKS BL1870.94644296STEAMBOAT LN21599ROADHOUSE CT137.527620797EAGLE WIND DR256100STAGECOACH CI564.294259898BLUE OAKS BL1852104PRAIRIE WOODS WY377.057961199ROADHOUSE CT165105STEAMBOAT LN347.1145838100STAGE COACH CI557

 

[TheLos]

GIS_ID	GIS_ST	GIS_LEN		RM_ID	RM_ST	RM_LEN
88	OWL FEATHER CT	276.9406389		88	OWL FEATHER CT	312
89	TALON REACH CT	381.6816978		89	TALON REACH CT	373
92	EAGLE WIND DR	262.0329848		90	PACKARD DR	655
93	RUNNING WOLF WY	485.2580648		91	INDUSTRIAL AV	366
94	PRAIRIE WOODS WY	504.8662144		92	EAGLE WIND DR	258
95	RUNNING WOLF CT	273.9741478		93	RUNNING WOLF WY	476
96	STEAMBOAT LN	214.3477125		94	PRAIRIE WOODS WY	534
97	EAGLE WIND DR	262.0340082		95	RUNNING WOLF CT	315
98	BLUE OAKS BL	1870.946442		96	STEAMBOAT LN	215
99	ROADHOUSE CT	137.5276207		97	EAGLE WIND DR	256
100	STAGECOACH CI	564.2942598		98	BLUE OAKS BL	1852
104	PRAIRIE WOODS WY	377.0579611		99	ROADHOUSE CT	165
105	STEAMBOAT LN	347.1145838		100	STAGE COACH CI	557

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col></colgroup>

 

[FDibbins]

Your sample data - if that is indeed what it is - does not show what you want??

 

[TheLos]

Yes - that is sample data (sorry, I'm new to posting)

As an example:

ID's 88 match so calculate the difference in "LEN" (276.9 - 312 = -35), so then I want ID 88 returned. If there is no match, or difference is less than +/-30 then do not return.

I hope this makes sense...please let me know.

 

[TheLos]

To clarify the "match" - I don't mean match in that row...but a match in each data set

 

[FDibbins]

OK sorry, didn't see that there were 2 tables there. TRy this...

	A​	B​	C​	D​	E​	F​	G​	H​
1​	GIS_ID	GIS_ST	GIS_LEN		RM_ID	RM_ST	RM_LEN
2​	88	OWL FEATHER CT	276.9406389​		88	OWL FEATHER CT	312​	35.05936​
3​	89	TALON REACH CT	381.6816978​		89	TALON REACH CT	373​
4​	92	EAGLE WIND DR	262.0329848​		90	PACKARD DR	655​
5​	93	RUNNING WOLF WY	485.2580648​		91	INDUSTRIAL AV	366​
6​	94	PRAIRIE WOODS WY	504.8662144​		92	EAGLE WIND DR	258​
7​	95	RUNNING WOLF CT	273.9741478​		93	RUNNING WOLF WY	476​
8​	96	STEAMBOAT LN	214.3477125​		94	PRAIRIE WOODS WY	534​
9​	97	EAGLE WIND DR	262.0340082​		95	RUNNING WOLF CT	315​	41.02585​
10​	98	BLUE OAKS BL	1870.946442​		96	STEAMBOAT LN	215​
11​	99	ROADHOUSE CT	137.5276207​		97	EAGLE WIND DR	256​
12​	100	STAGECOACH CI	564.2942598​		98	BLUE OAKS BL	1852​
13​	104	PRAIRIE WOODS WY	377.0579611​		99	ROADHOUSE CT	165​
14​	105	STEAMBOAT LN	347.1145838​		100	STAGE COACH CI	557​

H2=IFERROR(IF(ABS(G2-INDEX(C:C,MATCH(E2,A:A,0)))>30,G2-INDEX(C:C,MATCH(E2,A:A,0)),""),"")
copied down as needed

 

[TheLos]

YES!

...but instead of returning the difference, how do I return the ID?

 

[FDibbins]

The bolded part of the formula is doing the heavy lifting...
=IFERROR(IF(ABS(G2-INDEX(C:C,MATCH(E2,A:A,0)))>30,G2-INDEX(C:C,MATCH(E2,A:A,0)),""),"")
The match is based in the ID, so if you just want he ID, perhaps this?
=IFERROR(IF(ABS(G2-INDEX(C:C,MATCH(E2,A:A,0)))>30,E2,""),"")

 

[TheLos]

Amazing! Thank you so much!

 

[FDibbins]

Happy to help, thanks for the kind words