Function Problems

Excel Lookup Formula for Household Income and AMI Levels

Maira Mehtab Sep 21, 2026 869 views

Question details

The user needs a formula to compare household size and annual income against an income table on a separate worksheet to retrieve the corresponding Area Median Income (AMI) level.

Product: Excel
Device & OS: not provided
Scenario: Calculating Area Median Income (AMI) levels based on demographic data using two-way lookup formulas across multiple worksheets.
Observed behavior: The user needs to cross-reference multiple criteria (household size and income) to return a specific value, but requires help structuring an INDEX, MATCH, or array formula against their specific data layout.

Before you start

Before applying complex lookup formulas, ensure your income table is properly formatted with household sizes in one dimension (e.g., columns) and income limits in the other (e.g., rows). If you are asking for formula help online, always prepare a stripped-down copy of your workbook without sensitive information.

Solution 1Recommended

Use INDEX and MATCH for a Two-Way Lookup

Combine INDEX and MATCH functions to cross-reference both household size and annual income against your AMI table.

A standard VLOOKUP only searches one column. To match both a row (Income) and a column (Household Size) to find the intersecting AMI level, the INDEX and MATCH combination is required.

Organize your reference table

Set up your AMI limits on a separate worksheet. Place 'Household Size' numbers across the top row (e.g., B1:I1) and the 'AMI Levels' (e.g., 30%, 50%, 80%) down the first column (e.g., A2:A10), filling the grid with the corresponding maximum income values.

Select the target cell

Navigate back to your main demographic data worksheet and click on the cell where you want the resulting AMI level to be displayed.

Construct the MATCH formulas

Use one MATCH function to find the closest income tier (using match_type 1 or -1 for approximate matches) and a second MATCH function to locate the exact household size column (using match_type 0).

Combine with INDEX

Wrap the MATCH functions inside an INDEX formula. The structure will look like: =INDEX(AMI_Levels_Range, MATCH(Target_Income, Income_Column, 1), MATCH(Target_Size, Size_Row, 0)). Press Enter to calculate the result.

Approximate Matching: When dealing with incomes that fall between exact tiers, ensure your income table is sorted in ascending order and use '1' as the match_type in your MATCH formula to find the appropriate bracket.

Solution 2

Share a Sanitized Workbook for Formula Troubleshooting

When your lookup formula fails and you need community support, provide an accessible, safe version of your file for experts to review.

Easily Calculate AMI Levels with WPS Spreadsheet

WPS Office Spreadsheet fully supports advanced lookup functions, including INDEX, MATCH, and XLOOKUP. You can seamlessly process complex demographic data, cross-reference household incomes, and calculate AMI levels with high performance and full compatibility with Microsoft Excel formats.

1. Open your dataset: Launch WPS Spreadsheet and open your workbook containing the household income and size data.
2. Access lookup formulas: Navigate to the Formulas tab on the ribbon and click on Lookup & Reference to find the INDEX and MATCH functions.
3. Input your criteria: Select your target cell, enter your combined INDEX and MATCH formula referencing the separate AMI table worksheet.
4. Calculate instantly: Press Enter to evaluate the formula, then drag the fill handle down to apply the AMI lookup to all households in your dataset.

100% compatibility with Microsoft Excel formulas and .xlsx file formats.Full support for advanced array formulas, INDEX, MATCH, and modern XLOOKUP features.Free, lightweight, and fast processing for large demographic datasets.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my INDEX MATCH formula return an #N/A error?

An #N/A error means the MATCH function cannot find the lookup value. Ensure that your household size and income data types match the reference table (e.g., both are formatted as numbers, not text) and that there are no hidden trailing spaces in the cells.

Can I use XLOOKUP instead of INDEX and MATCH for this task?

Yes. If you are using a recent version of Excel or WPS Office, you can nest two XLOOKUP functions to perform a two-way lookup (one for household size and one for income). This is often easier to write and read than the traditional INDEX and MATCH method.

How do I look up values falling between two income brackets?

To find an approximate match for income tiers, set the match_type argument in your MATCH function to 1 (less than) or -1 (greater than). Be sure your income table is sorted in ascending or descending order to match the chosen argument.

Excel & Data Problems