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:
Jon, why don't you ROUND to cents your totals and intermediate results? Precision is always exists in calculations, even =1*(0.5-0.4-0.1) is not equal to zero in Excel.
To answer the question: "why don't you ROUND to cents your totals and intermediate results?" I doing a check to match a statement's total to a list of deposits -- the statements deal with fractions of a cent, and there is no guarantee that rounding errors won't propagate enough that I'd never find a match. I can make all sorts of data-entry errors, often because the statements are printed with teeny-tiny fonts & my eyes aren't so good. So, this check is important for me to catch errors. (Nowadays, now that the statements track fractions of a cent, the statements also come in .CSV format, making it easier to run a pivot table & do a copy-paste of values and thereby lessening my user errors).