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]

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

 

[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...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][/tr]
[tr][td]

1​

[/td][td]GIS_ID[/td][td]GIS_ST[/td][td]GIS_LEN[/td][td][/td][td]RM_ID[/td][td]RM_ST[/td][td]RM_LEN[/td][td][/td][/tr]

[tr][td]

2​

[/td][td]88[/td][td]OWL FEATHER CT[/td][td]

276.9406389​

[/td][td][/td][td]88[/td][td]OWL FEATHER CT[/td][td]

312​

[/td][td]

35.05936​

[/td][/tr]

[tr][td]

3​

[/td][td]89[/td][td]TALON REACH CT[/td][td]

381.6816978​

[/td][td][/td][td]89[/td][td]TALON REACH CT[/td][td]

373​

[/td][td][/td][/tr]

[tr][td]

4​

[/td][td]92[/td][td]EAGLE WIND DR[/td][td]

262.0329848​

[/td][td][/td][td]90[/td][td]PACKARD DR[/td][td]

655​

[/td][td][/td][/tr]

[tr][td]

5​

[/td][td]93[/td][td]RUNNING WOLF WY[/td][td]

485.2580648​

[/td][td][/td][td]91[/td][td]INDUSTRIAL AV[/td][td]

366​

[/td][td][/td][/tr]

[tr][td]

6​

[/td][td]94[/td][td]PRAIRIE WOODS WY[/td][td]

504.8662144​

[/td][td][/td][td]92[/td][td]EAGLE WIND DR[/td][td]

258​

[/td][td][/td][/tr]

[tr][td]

7​

[/td][td]95[/td][td]RUNNING WOLF CT[/td][td]

273.9741478​

[/td][td][/td][td]93[/td][td]RUNNING WOLF WY[/td][td]

476​

[/td][td][/td][/tr]

[tr][td]

8​

[/td][td]96[/td][td]STEAMBOAT LN[/td][td]

214.3477125​

[/td][td][/td][td]94[/td][td]PRAIRIE WOODS WY[/td][td]

534​

[/td][td][/td][/tr]

[tr][td]

9​

[/td][td]97[/td][td]EAGLE WIND DR[/td][td]

262.0340082​

[/td][td][/td][td]95[/td][td]RUNNING WOLF CT[/td][td]

315​

[/td][td]

41.02585​

[/td][/tr]

[tr][td]

10​

[/td][td]98[/td][td]BLUE OAKS BL[/td][td]

1870.946442​

[/td][td][/td][td]96[/td][td]STEAMBOAT LN[/td][td]

215​

[/td][td][/td][/tr]

[tr][td]

11​

[/td][td]99[/td][td]ROADHOUSE CT[/td][td]

137.5276207​

[/td][td][/td][td]97[/td][td]EAGLE WIND DR[/td][td]

256​

[/td][td][/td][/tr]

[tr][td]

12​

[/td][td]100[/td][td]STAGECOACH CI[/td][td]

564.2942598​

[/td][td][/td][td]98[/td][td]BLUE OAKS BL[/td][td]

1852​

[/td][td][/td][/tr]

[tr][td]

13​

[/td][td]104[/td][td]PRAIRIE WOODS WY[/td][td]

377.0579611​

[/td][td][/td][td]99[/td][td]ROADHOUSE CT[/td][td]

165​

[/td][td][/td][/tr]

[tr][td]

14​

[/td][td]105[/td][td]STEAMBOAT LN[/td][td]

347.1145838​

[/td][td][/td][td]100[/td][td]STAGE COACH CI[/td][td]

557​

[/td][td][/td][/tr]
[/table]
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