Function Problems

How to Return an Award from a Table Array Using Excel Formulas

Maira Mehtab Sep 27, 2026 869 views

Question details

The user needs an Excel formula to look up a value within a table array to determine if an individual qualifies for an award, displaying a blank cell if they do not meet the minimum requirements.

Product: Excel
Device & OS: not provided
Scenario: Evaluating an individual's total score or performance against a 2-dimensional reference table to assign the correct award tier.
Observed behavior: The correct award tier should be returned when criteria are met, and the cell must display as completely blank instead of showing an error when the total falls short.

Before you start

Ensure your reference table is properly formatted with clear column and row headers. Familiarize yourself with the absolute cell references (using the $ sign) required to lock your table array when dragging the formula to other cells.

Solution 1Recommended

Use INDEX and XMATCH Wrapped in IFERROR

This solution combines the dynamic lookup capabilities of INDEX and XMATCH with IFERROR to handle exact and approximate match thresholds while suppressing errors for non-qualifying totals.

The core of this formula utilizes a 2-dimensional lookup. The inner XMATCH determines the correct row based on your first criterion, while the outer XMATCH evaluates the specific value against the minimum thresholds.

By setting the XMATCH search mode to -1, the formula searches for an exact match or the next smaller item, which is perfect for point-based award tiers. Finally, wrapping the entire formula in IFERROR replaces standard Excel errors with a clean, blank cell.

Select the target cell

Click on the cell where you want the award result to be displayed (for example, cell I3).

Enter the formula

Type the formula exactly as follows: =IFERROR(INDEX($L$2:$N$2, XMATCH(G3, INDEX($L$4:$N$16, XMATCH(H3, $K$4:$K$16), 0), -1)), "")

Apply and drag

Press Enter to calculate the result. If you have a list of individuals, click the bottom-right corner of cell I3 and drag the fill handle down to apply the formula to the remaining rows.

Adjusting Cell References: Make sure to adjust the ranges ($L$2:$N$2, $L$4:$N$16, etc.) and lookup values (G3, H3) in the formula to match the actual layout of the data in your worksheet.

Advanced Data Management

Seamlessly Handle Complex Lookups in WPS Spreadsheet

WPS Spreadsheet fully supports advanced modern array functions like XMATCH, INDEX, and IFERROR. You can execute complex multi-criteria lookups with zero compatibility issues while enjoying a highly optimized data processing experience.

1. Open your workbook: Launch WPS Spreadsheet and open the .xlsx file containing your awards data.
2. Input the lookup formula: Select the designated award cell and paste your INDEX and XMATCH formula.
3. Review the results: Hit Enter to calculate. WPS Spreadsheet will instantly display the qualifying award or leave the cell blank for non-qualifying totals.

Fully compatible with Microsoft Excel formulas and native .xlsx formats.Natively supports modern lookup functions including XLOOKUP and XMATCH.Lightweight application that processes large table arrays rapidly.Free to use with an intuitive, user-friendly interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my formula return a #N/A error instead of a blank space?

A #N/A error occurs when the lookup function cannot find a valid match in the table array. To fix this and display a blank space, you must wrap your entire lookup formula in the IFERROR function, like this: =IFERROR(your_formula, "").

Can I use VLOOKUP instead of INDEX and XMATCH for an award table?

While VLOOKUP works well for simple one-dimensional searches, it is much harder to use when you need to match both a row (e.g., department or category) and a column (e.g., varying point tiers). INDEX and XMATCH provide the flexibility needed for multi-criteria, 2-dimensional lookups.

What does the -1 mean in the XMATCH formula?

In the XMATCH function, the -1 argument represents the match mode. It tells Excel to search for an exact match first, and if one is not found, to return the next smaller item. This is essential for calculating tiered awards where a score falls between two minimum thresholds.

Excel & Data Problems