Formula Errors

Why Excel MATCH with a Greater-Than Test Requires an Array Formula

John WilsonJohn Wilson Nov 20, 2026 868 views

Question details

Understand why a MATCH formula that tests a range for values greater than zero may require array entry, while a direct search for 1 does not.

Why Excel MATCH with a Greater-Than Test Requires an Array Formula
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user wants to find the position of the first positive cash flow in Cashflow!E123:BL123.
Observed behavior
The comparison Cashflow!E123:BL123>0 produces an array of TRUE and FALSE values, while MATCH(1,Cashflow!E123:BL123,0) searches the existing cell values directly.
Before you start

Check your Excel version and confirm that Cashflow!E123:BL123 contains numeric values. Decide whether you need the first value greater than zero or an exact numeric 1; these searches are not equivalent.

Solution 1Recommended

Enter the logical MATCH formula as an array formula

Use this method to find the first positive value, especially in an older Excel version that requires array entry.

The expression Cashflow!E123:BL123>0 tests every cell and creates an array of TRUE and FALSE results. MATCH then searches that array for the first TRUE.

Modern Excel versions with dynamic array support can normally evaluate this formula with Enter. Older versions generally require Ctrl+Shift+Enter.

1
Enter the formula

Select a result cell and type =MATCH(TRUE,Cashflow!E123:BL123>0,0).

2
Confirm the formula

Press Enter in a modern Excel version. In an older version, press Ctrl+Shift+Enter instead; Excel adds braces around the formula in the formula bar.

3
Interpret the result

Read the result cell: 1 means the first matching value is in E123, 2 means F123, and so on. A #N/A result means no match was found.

Enter the logical MATCH formula as an array formula
Array braces: Do not type the braces yourself. In older Excel versions, use Ctrl+Shift+Enter to create the array formula.
Find positive cash flows

Find the First Positive Cash Flow in WPS Spreadsheets

Use a helper row in WPS Spreadsheets to mark positive values and find the first match without relying on array-formula entry.

  1. 1. Open the workbook: Open WPS Spreadsheets and use Open to select the workbook containing the Cashflow sheet.
  2. 2. Create a helper row: Add a new worksheet, select E1, and enter =IF(Cashflow!E123>0,1,0).
  3. 3. Fill across: Drag the fill handle from E1 through BL1 to test each corresponding cash-flow cell.
  4. 4. Find the first match: In an empty cell, enter =MATCH(1,E1:BL1,0). The result is the position within the helper row, starting with 1 for column E.
Find the position of the first positive cash flow with MATCHUse a helper row to make the logical test easy to inspectOpen and edit Microsoft Excel XLSX and XLS filesWork in a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

Why does MATCH(TRUE,range>0,0) need array entry in older Excel?

The comparison tests multiple cells and produces an array of TRUE and FALSE values. Older Excel versions generally require Ctrl+Shift+Enter to pass that array to MATCH.

Does MATCH(1,range,0) find any positive number?

No. With 0 as the match type, MATCH searches for an exact numeric 1. It does not match other positive numbers such as 2 or 0.5.

How can I turn the TRUE and FALSE results into 1 and 0?

Multiply the comparison by 1, as in =(Cashflow!E123:BL123>0)*1. In older Excel versions, a formula using this array may still require Ctrl+Shift+Enter.

What does the number returned by MATCH represent?

It is the position within the searched range, not the worksheet column number. For Cashflow!E123:BL123, a result of 1 refers to E123 and 2 refers to F123.