Forum Discussion
"Close enough" MATCH
Jon, for better understanding how the formula works you may stay on the cell with formula (any one) and on ribbon Formulas->Evaluate Formula to see step by step what formula calculates.
Alternatively in formula bar you may select part of the formula which performs this or that calculation, F9 to see result of it and Ctrl+Z to return back.
As for you questions
1) Yes, arithmetic operation of single value and array performs that operation on each element of the array. You may try =SUM({2,3}+2), it returns 9 and intermediate calculation is =SUM({4,5}). Check with evaluate. Thus yes, that part of the formula returns an array like
{abs(a1-b1), abs(a1-b2), abs(a1-b3), abs(a1-b4)}
2) Correct, that will be an array with TRUE and FALSE
3) Double dash transform logical value to its numeric equivalent.
=--TRUE returns 1 (as well as =TRUE*1 and =TRUE+0)
=--FALSE returns 0, similar to above
4) If you need position, not the value on that position, as Detlef mentioned just don't use outer INDEX
=MATCH(1,INDEX(--(ABS(A1-B1:B4)<=0.05),0,),0)
In addition, inner INDEX here allows to avoid using of array formula, it returns resulting after ABS calculations array directly to MATCH. Alternatively you may use array formula (Ctrl+Shift+Enter)
=MATCH(1,--(ABS(A1-B1:B4)<=0.05),0)
which returns the same result:
Hi Jon,
Here is the mockup
=INDEX(B1:B4,MATCH(1,INDEX(--(ABS(A1-B1:B4)<=0.05),0,),0))
and in attached
Thanks. The good news -- your solution appears to work. The bad news -- I don't fully understand your solution. Here are my questions:
- ABS(A1-B1:B4) -- I don't know what this construction, where an array (B1:B4) is subtracted from a single cell. Does this return an array, where each element of the array is the original array's value subtracted from the value in A1.
- (ABS(A1-B1:B4)<=0.05) -- Again, I don't know this construction, but since I'm presuming that the ABS function is returning an array, the "<=0.05" part then creates an array full of TRUE or FALSE values, depending on whether the values in B1:B4 are within 0.05 of the value in A1.
- --(ABS(A1-B1:B4)<=0.05) -- I don't know what the "--" means. What does it mean?
- Okay -- all I'm looking for the is position in the array of the matching "close enough" value. Thus, instead of returning "10.0005", I want it to return "3", as in the third element in the array where the "close enough" match is found.
So, thank you, but can I get a little more help?
Jon, for better understanding how the formula works you may stay on the cell with formula (any one) and on ribbon Formulas->Evaluate Formula to see step by step what formula calculates.
Alternatively in formula bar you may select part of the formula which performs this or that calculation, F9 to see result of it and Ctrl+Z to return back.
As for you questions
1) Yes, arithmetic operation of single value and array performs that operation on each element of the array. You may try =SUM({2,3}+2), it returns 9 and intermediate calculation is =SUM({4,5}). Check with evaluate. Thus yes, that part of the formula returns an array like
{abs(a1-b1), abs(a1-b2), abs(a1-b3), abs(a1-b4)}
2) Correct, that will be an array with TRUE and FALSE
3) Double dash transform logical value to its numeric equivalent.
=--TRUE returns 1 (as well as =TRUE*1 and =TRUE+0)
=--FALSE returns 0, similar to above
4) If you need position, not the value on that position, as Detlef mentioned just don't use outer INDEX
=MATCH(1,INDEX(--(ABS(A1-B1:B4)<=0.05),0,),0)
In addition, inner INDEX here allows to avoid using of array formula, it returns resulting after ABS calculations array directly to MATCH. Alternatively you may use array formula (Ctrl+Shift+Enter)
=MATCH(1,--(ABS(A1-B1:B4)<=0.05),0)
which returns the same result:
Hi Jon
Remove the outer INDEX().
Okay -- more problems with removing the outer INDEX:
I changed "INDEX(B1:B4,MATCH(1,INDEX(--(ABS(A1-B1:B4)<=0.05),0,),0))" to
"MATCH(1,INDEX(--(ABS(A7-B7:B10)<=0.05),0,))",
and I have the following questions:
- I still have the questions I posted above. I kinda don't want to use a formula that I don't understand better than I do this one.
- The formula doesn't seem to work totally -- if I change the value in cell A1 to 8, 9, or 10, the new formula does return the appropriate INDEX number, but if I change A1 to 7, I expected the new formula to return "1", but it returns 4. Indeed, if I put "2", or "20", or any other number not in the list, the formula returns "4".
Please help.
