+/- 1 when identifying pairs

[mrphilippines1994]

Hey guys - Need help here. kinda new here.

I need a formula to identify pairs with a +/- 1.

Example:

I'm looking for a pair with value of 100 but the only thing I have is 97,98,99 and 101. I need a formula where 100 will still be matched with the values 99 and 101.

Let me know if I'm still not clear. lol

cheers mate

 

[Legacy 143009]

Will it return an address or what else? Or just a true value?

 

[mrphilippines1994]

If possible if the 99 and 101 is in range, it will return as 100. Hope that answers your question. Thanks

 

[Legacy 143009]

=IF(ISERROR(MATCH(B1,A:A,0)),IF(ISERROR(MATCH((B1-1),A:A,0)),0,IF(OR(INDIRECT("A"&MATCH((B1-1),A:A,0)+1)=(B1+1),INDIRECT("A"&MATCH((B1-1),A:A,0)-1)=(B1+1)),1,0)),1)*B1

Place your data to column A. Place our lookup value to B1 cell. Paste the formula above anywhere you like. It will return your lookup value if there is a match. Otherwise it will retun 0.

 

[mrphilippines1994]

Thanks the flash!

 

[FormR]

Hi, here is another option that you can try - same set-up as @Flashbond

=IF(SUM(COUNTIFS(A:A,B1+{-1,0,1})),B1,0)

 

[Legacy 143009]

+{-1,0,1}

Aahh... I didn't know you can notate arrays manually.

 

[FormR]

Aahh... I didn't know you can notate arrays manually.

Hi, it's referred to as an array constant - some reading here if interested.

https://support.office.com/en-us/ar...formulas-477443EA-5E71-4242-877D-FCAE47454EB8

 

[Legacy 143009]

Hi, here is another option that you can try - same set-up as @Flashbond

=IF(SUM(COUNTIFS(A:A,B1+{-1,0,1})),B1,0)

I think your formula has a flow. Place numbers 97. 98. 99. 101, 104.

Your formula is returning TRUE when you lookup for 102 even it doesn't match a +/-1 pair.

 

[FormR]

I think your formula has a flow. Place numbers 97. 98. 99. 101, 104.

Your formula is returning TRUE when you lookup for 102 even it doesn't match a +/-1 pair.

Maybe I'm not understanding, I thought it was simply - does a number exist that is either +1 / -1 or equal to the value being searched.

If there needs to be more than one match, then maybe this slight amendment.

=IF(SUM(COUNTIFS(A:A,B1+{-1,0,1})>1),B1,0)