+/- 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

 

[mrphilippines1994]

Not sure but does it detect decimal places? like if should be rounding things.

 

[FormR]

Not sure but does it detect decimal places? like if should be rounding things.

Hi, maybe you can have another go at explaining what you want, especially in regards to numbers with decimals. Try to include a couple of small examples and what the expected result would be for those examples.

 

[mrphilippines1994]

How about if the values has decimal place?

 

[mrphilippines1994]

disregard. I'll provide sample and better explanation shortly

 

[mrphilippines1994]

hello mate - so here it goes.

I have this reference value of 100 in one tab and the second tab i have available values of 104.50, 102.12, 98.35, 100.59 & 97.61.

I want to have a result where when I vlookup, it will match the 100.59 since there's a threshold of +/- 1 but I want it to return as 100 same as the reference number.

Hope it makes sense. tnx

 

[FormR]

Hi, so if there is at least one match that is within +1 or -1 of the lookup value return the lookup value?

If so, here is one option that you can adapt to your two sheet set-up.

Excel 2013/2016
	A	B	C	D
1	104.5		100	100
2	102.12
3	98.35
4	100.59
5	97.61
Sheet1

Cell Formulas
Range	Formula
D1	=IF(COUNTIFS(A:A,">="&C1-1,A:A,"<="&C1+1),C1,0)

 

[mrphilippines1994]

holy cow. it work!

Tnx a lot! but i'll be back cause i will use it along with multiple formulas.