ISNA function

[jkopec]

Hi all,

I have a spreadsheet that I'm using to compare financial information
(reconcile bank statements). I am using a formula that looks at a
number and then searches another column to see if that number
matches. Example =ISNA(MATCH(A1, B1:B50, FALSE)) If the result is
FALSE, it tells me that the number is in the next column. If it's
TRUE, the number doesn't match.

The number that is in A1 is coming from another sheet. The cell on
the other sheet contains a simple formula which is just adding the
cash and checks together to come up with the total deposit. The
numbers that are in B1:B50 are copied and pasted from another
spreadsheet that was downloaded off the banks website.

There is one deposit that is not matching up to the bank list. This
number is actually there though. If I change the cell that A1 is
pulling from (change it from a formula to a manually entered value)
the ISNA formula works. Also, if I change the cash or the check
numbers by 1 cent (so the deposit is one cent different), and change
the bank number by one cent, the formula works. The formula just
doesn't seem to like that one number.

Has anyone ever seen this before?

Thanks for any help you can offer!
Jennifer

 

[Tim879]

Try rounding the number. Sometimes - I still don't know why - Excel
adds insignificant digits to a number. If you round the number on your
linked spreadsheet (the one where you add up all the bank deposits /
checks) then this should solve your problem.

the formula would be =ROUND(sum(b1:b50),2)

 

[Pete_UK]

It might be that the number is not exactly what it seems - for
example, it might look like a value of 12.67, but if you increase the
number of decimal places then you might see 12.6666667.

You could think about using ROUND in your formula, rather than trying
to match exactly where you might have small discrepancies like this to
contend with.

Hope this helps.

Pete

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

Thanks so much!!! It worked!