Function Problems

How to Match Whole Numbers with Decimal Values in Excel

Amos Gikunda Sep 27, 2026 868 views

Question details

The user needs a way to match whole numbers in one column with the integer portion of decimal numbers in another column, returning a TRUE result for matches.

How to Match Whole Numbers with Decimal Values in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Comparing two lists of numbers where one list contains whole numbers and the other contains decimals, seeking exact matches on the integer level.
Observed behavior: The goal is to return a TRUE boolean value when the whole number in Column B successfully matches the rounded-down integer portion of a decimal in Column A.

Before you start

Ensure that your data columns are formatted as numbers and remove any leading or trailing text spaces that might interfere with formula calculations.

Solution 1Recommended

Use the INT and MATCH Functions to Compare Integer Values

Extract the integer portion of your decimal values using the INT function, then use MATCH and ISNUMBER to check for exact whole-number matches.

This is the most direct method to solve the problem. The INT function automatically strips away the decimal portion (e.g., turning 4760.001 into 4760). By nesting this inside a MATCH function, Excel can compare the whole numbers accurately.

Select the target result cell

Click on the first cell in Column C (e.g., C1) where you want the TRUE or FALSE result to appear.

Enter the matching formula

Type the formula =IF(ISNUMBER(MATCH(B1,INT(A:A),0)),TRUE,FALSE) into the formula bar.

Confirm as an array formula (if necessary)

Depending on your specific version of Excel, comparing an entire column array like INT(A:A) might require you to press Ctrl + Shift + Enter instead of just Enter.

Drag the fill handle

Click and hold the small square at the bottom-right corner of cell C1, then drag it down to apply the formula to the remaining rows in your data set.

Use the INT and MATCH Functions to Compare Integer Values

Understanding the INT Function: The INT function rounds a number down to the nearest integer. This allows the MATCH function to evaluate both columns as whole numbers without permanently altering your original decimal data.

Solution 2

Create a Helper Column for Simpler Formulas

If you are new to Excel and complex nested formulas are intimidating, using a helper column simplifies the process by separating the calculation steps.

Efficient Data Processing with WPS

Easily Match and Process Complex Data with WPS Spreadsheet

WPS Spreadsheet provides seamless support for all standard Excel functions, including INT, MATCH, and ISNUMBER. You can solve complex data matching tasks quickly and accurately with its built-in dynamic array support.

1. Open your workbook in WPS: Launch WPS Office and open your .xlsx spreadsheet containing the whole numbers and decimal values.
2. Apply the INT and MATCH formula: Select the target cell in your result column and enter =IF(ISNUMBER(MATCH(B1,INT(A:A),0)),TRUE,FALSE).
3. Calculate and auto-fill: Hit Enter to calculate the match result. Double-click the fill handle at the bottom-right corner of the cell to apply the matching logic to all your data rows instantly.

Fully compatible with Microsoft Excel formulas and .xlsx file formats.Built-in dynamic array support for modern and rapid formula execution.Lightweight installation with a clean, user-friendly tabbed interface.Completely free to use across Windows, Mac, Linux, iOS, and Android.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my formula return an error (#N/A or #VALUE!) instead of TRUE or FALSE?

This usually happens if the data ranges contain text formatted as numbers, or if you are using an older version of Excel that does not support dynamic arrays. Ensure columns A and B are formatted as Numbers. If using an older version, evaluating an entire column array like INT(A:A) requires executing it as an array formula by pressing Ctrl + Shift + Enter.

What is the difference between the INT and TRUNC functions in this scenario?

For positive numbers, INT and TRUNC will yield the same result (e.g., both turn 4760.001 into 4760). However, for negative numbers, INT rounds down to the lower integer (e.g., -4.3 becomes -5), whereas TRUNC simply drops the decimal (e.g., -4.3 becomes -4). If your data includes negative numbers, TRUNC might be the better choice.

Can I highlight the matched numbers in Column B instead of returning TRUE in a separate column?

Yes, you can use Conditional Formatting. Select the data in Column B, navigate to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter the formula =ISNUMBER(MATCH(B1,INT($A:$A),0)). Choose a highlight fill color and click OK.

Excel & Data Problems