Using an IF formula to match data

[wkl1008]

Good afternoon all,

I've been using an if statement to do a data reconciliation. The data element I'm trying to reconcile (match) are Award Numbers for government grants such as FL-91-01 or NV-92-20. I'm using the forumla =IF(F1380=N1380,"","mismatch") where the Award Numbers from one system are located in column F and the Award Numbers from the other system are in column N. This produces the correct result for almost 98% of the data, but I do get some false negatives where the data seems to match but my formula produces a "mismatch" result. I've checked the format and they all seem to be consistent (Text, Calibriri, 11 font) so I'm not sure where the hang up is. Any guidance from the community is much appreciated!

[Richard Buttrey]

Hi,

Usually the problem is trailing or perhaps leading space characters. Since you can't 'see' space characters they are difficult to spot. Try using the TRIM() function with the codes you're using. This won't permanently remove any space characters but it will ignore them in any matching formulae.

Where you think there is an apparent anomaly, look at the text string of the relevant cell in the formula bar. If you click the cursor somewhere to the right of the string in the formula bar you'll probably find it jumps back to a position somewhere after the last visible character

HTH