Excel how to find values in 1 column exist in the range of values in another (approximate)

Question

How can I search if values in one cell (column A) exist in column B. With an approximate threshhold of +/- .5

For instance:

Cell A2: 100.26
Column B: 100.30

Is there a formula that can search A2 within all of column B for an approximate match +/- .5 to return true/false?

Any interest in finding in which rows the "close" values exist ?? — Gary's Student, Oct 28 '16 at 20:06

JNevill · Answer 1 · 2016-10-31T14:47:24.203

3

You can use COUNTIFS() for this:

 =COUNTIFS(B:B, "<" & A2 + 0.5,B:B, ">" & A2 - 0.5)

This tests values in Column B twice. Once to see if there is a value less then A2+.5 and then again to see if that value is also greater than A2 - .5

If you want this to return True/False, just turn it into an inequality:

 =COUNTIFS(B:B, "<" & A2 + 0.5,B:B, ">" & A2 - 0.5)>0

Update with Example

To show this working, put value 10 in cell A2. Then in B1 though B5 put the following list:

Now in C1 (or any cell on the same tab that isn't A2 or in Column B) put the formula from above:

 =COUNTIFS(B:B, "<" & A2 + 0.5,B:B, ">" & A2 - 0.5)>0

And it will spit out "True" because the value 10 that is in cell A2 is within +/-5 from a value that exists in Column B.

edited Oct 31 '16 at 14:47

answered Oct 28 '16 at 19:33

JNevill

I'm having some trouble getting this forumla to work. How would it be inputted exactly in an table of data shown such as: a b 18,147.47 7,883.03 8,603.98 0.00 3,923.62 9,025.00 10,612.75 801.19 260.59 260.59 1,488.58 1,488.59 9,025.00 611.93 189.26 7,883.06 – Gordon Fung Oct 31 '16 at 14:32
Much appreciated! – Gordon Fung Oct 31 '16 at 15:00

score 0 · Answer 2 · answered Oct 28 '16 at 19:34

0

Add 2 helper columns C and D where C is =B1-0.5 and fill down and D is =B1+0.5 and filled down.

Then =if(and(a2>C2,a2<D2,TRUE,FALSE)

answered Oct 28 '16 at 19:34

atclaus