How to Return an AMI Level with INDEX and MATCH in WPS Spreadsheets
Question details
The user needs an INDEX/MATCH formula that returns an AMI percentage level by matching a household size to a row and comparing an adjusted income against the thresholds in that row.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has an AMI lookup table with household sizes in a column and AMI percentage thresholds in labeled columns. They need to look up the correct AMI level for a given household size and income.
- Observed behavior
- The desired formula should match the household size to the correct row, compare the income value with the row thresholds, and return the corresponding AMI percentage range from the header row.
Arrange the AMI table so the percentage labels are in B6:G6, household sizes are in A7:A14, and income thresholds fill each row. Confirm whether your WPS Spreadsheets version uses dynamic arrays; older array formulas may require Ctrl+Shift+Enter.
Use INDEX, MATCH, and OFFSET to return the AMI level
Use this formula when your AMI table has percentage labels in a header row, household sizes in the first column, and income thresholds that are sorted in ascending order within each row.
The core formula first matches the household size to a row, uses OFFSET to return that row's thresholds, and then uses MATCH with match type 1 to find the last threshold less than or equal to the income. Adding 1 moves to the AMI level that contains the income.
Replace B2 with the cell that holds the income value and B3 with the cell that holds the household size. Keep the absolute references so you can copy the formula without breaking the lookup range.
Click the cell where you want the AMI percentage level to appear.
Type =INDEX($B$6:$G$6,MATCH(B2,OFFSET($B$6:$G$6,MATCH(B3,$A$7:$A$14,0),0),1)+1) into the formula bar.
Replace B2 with the cell containing the adjusted income and B3 with the cell containing the household size.
In dynamic array versions, press Enter. In older WPS Spreadsheets or Excel versions, press Ctrl+Shift+Enter to enter it as an array formula.
Test with several income and household-size combinations to confirm the result changes to the expected AMI percentage range.
Troubleshoot common INDEX/MATCH AMI formula errors
Use this if the formula returns #N/A, #REF!, or an unexpected AMI level.
Return AMI Levels in WPS Spreadsheets with INDEX and MATCH
WPS Spreadsheets supports INDEX, MATCH, and OFFSET, so you can build the AMI lookup formula directly in a free, familiar spreadsheet environment. Open your existing workbook, apply the formula, and keep full compatibility with Excel .xlsx files.
- 1. Open your AMI workbook: Launch WPS Spreadsheets and open the workbook that contains the AMI lookup table.
- 2. Select the result cell: Click the cell where the AMI level should appear and open the formula bar.
- 3. Enter the formula: Type =INDEX($B$6:$G$6,MATCH(B2,OFFSET($B$6:$G$6,MATCH(B3,$A$7:$A$14,0),0),1)+1), replacing B2 and B3 with your cells.
- 4. Confirm with Enter or Ctrl+Shift+Enter: Press Enter in dynamic array versions, or Ctrl+Shift+Enter if your version requires legacy array formula entry.
- 5. Save as .xlsx: Go to Menu > Save As and choose Excel Workbook (.xlsx) to keep the file compatible with Microsoft Office.
Frequently Asked Questions
Why does the AMI formula return #N/A even though the household size looks correct?
Check that the household-size cell exactly matches a value in A7:A14 and that the income is at least as large as the first threshold in the matched row. Extra spaces, text-formatted numbers, or incomes below the first threshold can all produce #N/A.
Do I have to press Ctrl+Shift+Enter in WPS Spreadsheets?
It depends on your version. In current dynamic array versions, Enter is enough. In older versions, select the formula cell, press F2, and then press Ctrl+Shift+Enter to enter it as an array formula.
What does the +1 after MATCH do in this AMI formula?
MATCH with match type 1 returns the position of the last threshold that is less than or equal to the income. Adding 1 selects the next percentage label, meaning the income falls within that AMI range.
Can I use this formula if my AMI table starts in a different cell?
Yes. Replace $B$6:$G$6 and $A$7:$A$14 with the actual header and household-size ranges in your workbook, keeping the same structure.
