Formula Errors

How to Return the First Value Greater Than Another in Excel

Maira Mehtab Sep 27, 2026 870 views

Question details

The user needs a formula to find the first value in one column that exceeds the corresponding value in another column, retrieve a related value (such as a date), and highlight the matching cell.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Comparing two columns of numbers row-by-row to extract corresponding data based on the first occurrence of a greater-than condition.
Observed behavior: The user requires an array formula using INDEX and MATCH to evaluate the condition, retrieve the correct value, and apply conditional formatting.

Before you start

Ensure your data columns are properly aligned and contain numeric values. If you are using older versions of Excel, keep in mind that array formulas require a specific keystroke combination to calculate correctly.

Solution 1Recommended

Use an INDEX and MATCH Array Formula

Combine the INDEX and MATCH functions into an array formula to compare two columns and return the first matching value.

This method evaluates the condition across the entire specified range. Because it performs a row-by-row comparison, it must be entered as an array formula in older versions of Excel.

Select the target cell

Click on the cell where you want the resulting value to be displayed.

Enter the formula

Type the formula =INDEX(B1:B7,MATCH(TRUE,B1:B7>A1:A7,0)) into the formula bar. Adjust the ranges B1:B7 and A1:A7 to match your actual data columns.

Confirm as an array formula

Do not simply press Enter. Instead, press Ctrl + Shift + Enter simultaneously. Excel will wrap the formula in curly braces {}, indicating it is an array formula.

Modern Excel Versions: If you are using a modern version of Excel with dynamic arrays (like Microsoft 365), you can typically just press Enter instead of Ctrl+Shift+Enter.

Solution 2

Return a Related Value (e.g., Date) from Another Column

Adjust the INDEX range of your formula to return a corresponding value, such as a date, from a different column in the same row.

Solution 3

Highlight the Matching Cell with Conditional Formatting

Use conditional formatting to visually identify the first cell in a column that exceeds the corresponding value in another column.

Advanced Data Analysis

Use WPS Spreadsheet to Easily Compare Column Data

WPS Office offers robust support for array formulas, conditional formatting, and all standard functions like INDEX and MATCH. You can perform complex data comparisons seamlessly and accurately using WPS Spreadsheet.

1. Open your workbook: Launch WPS Spreadsheet and open the document containing your data columns.
2. Input the array formula: Select the target cell, enter your INDEX and MATCH formula, and press Ctrl+Shift+Enter.
3. Apply Conditional Formatting: Navigate to the Home tab, click Conditional Formatting, and set up your highlighting rules using the exact same formula.

Fully compatible with Microsoft Excel formulas and array functions.Built-in support for advanced conditional formatting rules.Lightweight and runs smoothly on a wide variety of devices.Free alternative with a highly familiar user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my formula return a #VALUE! or #N/A error?

If you see a #VALUE! error, you likely forgot to confirm the formula with Ctrl+Shift+Enter, which is required for array formulas in older versions of Excel. An #N/A error means that none of the values in the first column were greater than the corresponding values in the second column.

Can I return just the row number instead of the value?

Yes. To get the relative row number where the condition is first met, remove the INDEX function and use only the MATCH portion: =MATCH(TRUE,B1:B7>A1:A7,0).

How do I evaluate the entire column instead of a specific range?

You can use full column references in your formula, such as =INDEX(B:B,MATCH(TRUE,B:B>A:A,0)). However, using whole column references in array formulas can sometimes slow down calculation speed in large worksheets, so specific ranges are often recommended.

Excel & Data Problems