Excel MATCH Greater Than Not Working? Fixes That Work

Excel’s MATCH function does not treat a greater-than sign in its lookup value as a condition. For the first value strictly greater than a threshold, use an exact match against a TRUE/FALSE comparison; for an approximate match, choose the match type that fits your sorted data.

Why MATCH greater-than searches fail

The common confusion is between a comparison test and MATCH’s third argument, which controls the kind of match. Typing a value such as >30 as the lookup value does not tell MATCH to find numbers greater than 30. In an exact-match search, Excel looks for that literal text; in other situations, the formula may return an error or an unexpected result.

MATCH returns a position in a range, not the matching value itself. Its syntax is MATCH(lookup_value, lookup_array, [match_type]). The optional match type is often the key to understanding why a formula behaves differently than expected:

1 finds the largest value less than or equal to the lookup value. The lookup range must be sorted in ascending order.
0 finds an exact match. The range does not need to be sorted.
-1 finds the smallest value greater than or equal to the lookup value. The range must be sorted in descending order.

Neither approximate mode means “find the first value strictly greater than this threshold” in an arbitrary list. Match type 1 points in the opposite direction, while match type -1 includes a value equal to the threshold and depends on descending order.

Choose the formula for the result you need

If you want the first item in the range that is strictly greater than a threshold, compare each cell with the threshold, then ask MATCH to find TRUE in the resulting comparisons. For example, suppose A2:A6 contains 10, 20, 30, 40, and 50, and E2 contains 30. With those values in place, enter:

=MATCH(TRUE,A2:A6>E2,0)

The result is 4, the position of 40 within A2:A6. It is not the worksheet row number: the first cell in the searched range is position 1. Because the test is strictly greater than, 30 itself does not qualify. If E2 contains 35, the result is still 4.

In Microsoft 365 for Windows, press Enter; Excel can evaluate the comparison as an array for this formula. In older Excel versions that do not evaluate this array formula directly, confirm it with Ctrl+Shift+Enter rather than Enter alone. On Mac, the legacy array-entry shortcut is Control+Shift+Enter. Do not add braces manually; Excel adds them when needed.

Return the matching value or its related detail

Since MATCH returns a position, you can use INDEX to retrieve the value at that position. Assuming the numbers are in A2:A6 and E2 is the threshold of 30:

=INDEX(A2:A6,MATCH(TRUE,A2:A6>E2,0))

This returns 40. To return a related result instead, suppose A2:A6 holds the numbers and B2:B6 holds corresponding product names. With the threshold still in E2, use:

=INDEX(B2:B6,MATCH(TRUE,A2:A6>E2,0))

This returns the name in the fourth cell of B2:B6, the item paired with 40. The two ranges must have the same length and matching row alignment. If the first qualifying number is not the one you want—for example, you need the smallest number above the threshold regardless of row order—sort the data or use a method that explicitly selects the minimum qualifying value.

When approximate MATCH is the right choice

Approximate matching can be useful for bracket tables, such as shipping bands or score ranges, when the lookup data is correctly sorted. It is not a general substitute for a greater-than test. With A2:A6 sorted ascending as 10, 20, 30, 40, 50, and E2 set to 35, this formula uses match type 1:

=MATCH(E2,A2:A6,1)

It returns 3, because 30 is the largest value less than or equal to 35. It does not return 4 for 40. If E2 is exactly 30, it returns position 3. With match type -1, the range instead has to be sorted descending; that mode finds the smallest value greater than or equal to the lookup value, so equality still counts. For a strict “greater than” condition, the TRUE/FALSE comparison is clearer.

Sorting is not a cosmetic requirement for approximate MATCH. If the range is unsorted, an approximate result can be wrong or produce #N/A. Use exact mode or a direct comparison when order cannot be guaranteed.

Use XLOOKUP when you want the next larger result

In Microsoft 365 or Excel 2021 and later, XLOOKUP can be a shorter option when you want the corresponding result for an exact match or, if there is no exact match, the next larger value. Assuming A2:A6 contains the ascending numbers 10, 20, 30, 40, 50, B2:B6 contains their labels, and E2 contains 35:

=XLOOKUP(E2,A2:A6,B2:B6,,1)

The formula returns the label paired with 40. The fifth argument, 1, means exact match or next larger item; the fourth argument is left empty for the default not-found behavior. This is different from asking for the first cell, in the worksheet’s current row order, that passes a strict greater-than test. For threshold brackets, sort the lookup values ascending and confirm that “next larger” matches the rule you intend. If your Excel version does not include XLOOKUP, use INDEX and MATCH.

Check these common causes of errors

The match type is wrong. A third argument of 1 is approximate matching downward, not a greater-than operator. Use 0 with a TRUE/FALSE comparison for the strict test.
The formula searches for text instead of a condition. A criterion such as >30 is not automatically parsed as an operator by MATCH. Compare the range to the threshold, as in the working formula above.
The range contains numbers stored as text. A cell that looks like 40 may be text, so it may not behave as expected in a numeric comparison. Check the source data and convert values to genuine numbers where appropriate; changing the cell’s number format alone does not convert text to a number.
The approximate range is not sorted correctly. Match type 1 expects ascending order; -1 expects descending order. Do not rely on approximate results when the data is unsorted.
No value passes the test. If every entry is less than or equal to the threshold, the exact search for TRUE finds nothing and returns #N/A. Decide what the sheet should display in that case.
The formula is looking at the wrong range. Check that the comparison range and the range passed to INDEX begin and end on the intended rows, especially after copying a formula.

If you want a message instead of #N/A when nothing qualifies, and A2:A6 contains the values while E2 holds the threshold, use:

=IFERROR(MATCH(TRUE,A2:A6>E2,0),"No value greater")

For the example with E2 equal to 30, this still returns 4; if E2 is 50, it returns No value greater. IFERROR also catches other errors, so it can conceal a separate problem in the data or formula. If you are debugging, first inspect the original formula’s error rather than immediately wrapping it.

Know when another function is more suitable

The right function depends on whether you need a position, a value, or a count. MATCH is useful when you need a relative position and want to combine it with INDEX. XLOOKUP is convenient for returning a related value directly, when available in your Excel version. If you only need to know how many entries exceed a threshold, a counting function such as COUNTIF may be more appropriate than finding a position. If you need all qualifying rows rather than just the first one, Microsoft 365’s FILTER function can return multiple matches.

Also distinguish “first in the list” from “smallest value above the threshold.” The MATCH comparison returns the first qualifying cell in the range’s current order. If the values are 50, 40, and 60, with a threshold of 35, it returns the position of 50—even though 40 is the smallest qualifying number. Sorting ascending makes the first qualifying item the smallest, but changes the order of the underlying data unless you use a separate sorted result.

Frequently Asked Questions

Can MATCH find a number greater than a cell value?
Yes. Compare the lookup range with the threshold cell and find TRUE with exact match mode: MATCH(TRUE,range>threshold,0). In an actual worksheet, replace the placeholders with cell references and keep the comparison range aligned with any range used by INDEX.

Why does MATCH with 1 return a smaller number?
Match type 1 finds the largest value less than or equal to the lookup value, and expects ascending data. It is not the greater-than setting. For a value above the threshold, use a comparison test or a suitable next-larger lookup.

Does MATCH(TRUE,range>value,0) need Ctrl+Shift+Enter?
In Microsoft 365 on Windows, enter it normally. Older Excel versions may require Ctrl+Shift+Enter because the comparison produces an array. On Mac, use Control+Shift+Enter when working in a version that requires legacy array entry.

How do I get the actual result instead of a position?
Wrap the MATCH expression in INDEX, using the same range as the value source, or use XLOOKUP when its match behavior fits your case. Remember that MATCH’s number is a position within the selected range, not a worksheet row number.

What does #N/A mean in a greater-than MATCH formula?
It usually means no cell evaluated to TRUE, or the searched range is not the range you intended. Check the threshold, confirm that the values are numeric, and verify that at least one value is strictly greater. A value equal to the threshold does not qualify.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.