Array Formula NOT Returning All Desired Results

[HB333]

Hi,

The goal of this formula is to:

• 1st: Take a look at the value of "US 1" - find any values in that column that are +/- 2 to it
• 2nd: For those that fulfill the 1st criteria, then look at the value of "US 12" and find any values that are +/- 2 to it
• Overall - to be a "match", the values of US 1 have to be +/- 2 AND the values of US 12 have to be +/- 2

The following array formula is currently in D2:

{=IF($A2= "","",IF(($A$2:$A$500<>""),IFERROR(INDEX($A$2:$A$500,SMALL(IF(($B$2:$B$500>=$B2-2)*($B$2:$B$500<=$B2+2)*($C$2:$C$500>=$C2-2)*($C$2:$C$500<$C2+2)*(ROW($A$2:$A$500)<>ROW($A2))=0,($B$2:$B$500>=$B2-2)*($B$2:$B$500<=$B2+2)*($C$2:$C$500>=$C2-2)*($C$2:$C$500<=$C2+2)*(ROW($A$2:$A$500)<>ROW($A2))+100000,($B$2:$B$500>=$B2-2)*($B$2:$B$500<=$B2+2)*($C$2:$C$500>=$C2-2)*($C$2:$C$500<=$C2+2)*(ROW($A$2:$A$500)<>ROW($A2))*ROW($A$2:$A$500))-1,COLUMNS($D2:D2)),1),""),""))}

I've attached a worksheet with my current results.

Book1.xlsx

The following issues are arising:

1) K should match with L, M, and N -- the formula missed M
2) L should match with K -- the formula missed K
3) O should match with P, Q, R, S, and T -- the formula missed S
4) R should match with P, Q, and T -- the formula missed P and Q

Any idea how to fix/why this is occurring? Thank you again for your help - it is MUCH appreciated!!

[hemesh]

Try this in D2 Drag down and To right

=IFERROR(INDEX($A$2:$A$11,SMALL(IF(($B$2:$B$11=$B2+2)+($B$2:$B$11=$B2-2)+($C$2:$C$11=$C2+2)+($C$2:$C$11=$C2-2),ROW($A$2:$A$11)-ROW($A$2)+1),COLUMN(A1))),"") array entered (Ctrl+Shift+Enter) Key.


If this helps click "*" in the bottom left corner of my post!