Why Excel MATCH with a Greater-Than Test Requires an Array Formula
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.

- 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.
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.
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.
Select a result cell and type =MATCH(TRUE,Cashflow!E123:BL123>0,0).
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.
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.

Search directly for an existing numeric 1
Use a direct MATCH when the range already contains numeric 1 values and you specifically need to find the first 1.
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. Open the workbook: Open WPS Spreadsheets and use Open to select the workbook containing the Cashflow sheet.
- 2. Create a helper row: Add a new worksheet, select E1, and enter =IF(Cashflow!E123>0,1,0).
- 3. Fill across: Drag the fill handle from E1 through BL1 to test each corresponding cash-flow cell.
- 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.

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.




