Excel: Database Functions Not Returning Values as Expected

SUMMARY

When you are using Microsoft Excel database functions, if no matching records are returned even though the criteria matches certain records in the database, the problem may stem from the precision of the number inside the database.

When Excel performs any database function that relies upon certain criteria, the criteria must match the appropriate records exactly.

For example, if a particular database record has a field containing a number that is not an integer, but the field is formatted to show no decimal places, the number will not match a seemingly identical integer inside the criteria. Although the two values appear to be the same on the screen, they are not exact matches, and therefore the information will not be returned as expected.

One way to work around this behavior is to create a criteria that encompasses the formats of a particular number. For example, set the criteria to be ">=10 and <11" instead of "10".

Another solution is to alter the database to return a number that will satisfy the criteria. For example, use the ROUND or INT formulas in the database.

MORE INFORMATION

To determine if this behavior is causing the problem, activate a cell inside the database that appears to match the criteria, and check to see if the number that appears in the formula bar matches the criteria exactly.

If the database number is a formula, highlight the formula in the formula bar and press COMMAND+= to check its precision. Pressing COMMAND+= evaluates the formula and displays the exact value that the formula returns. (Once the formula has been evaluated, click the X in the formula bar to return the formula to normal.)