Replace Nested IF Statements with an Excel County Fee Lookup Table
Question details
Replace 120 nested IF statements with a maintainable lookup table that returns a fee for each county.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet needs to return the appropriate fee based on a county name.
- Observed behavior
- The proposed formula would exceed Excel's 64-level limit for nested functions.
Collect the county names and fees in two columns, with one fee per county. Check your Excel version: XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not Excel 2016.
Use XLOOKUP with a county fee table
Use this approach in an Excel version that supports XLOOKUP to make fee updates easy.
Enter County and Fee as column headers, then list each county and its fee below them. Select the range and choose Insert > Table, confirming that the table has headers.
Click inside the table and use Table Design > Table Name to name it feeTbl.
In the fee cell for the county in A2, enter =XLOOKUP(A2,feeTbl[County],feeTbl[Fee],"Not found").
Drag the formula cell's fill handle down for the remaining rows. Check that known counties return their fees and unmatched names return Not found.

Use INDEX and MATCH in Excel 2016
Use this alternative when XLOOKUP is unavailable.
Use VLOOKUP for an exact county match
Use VLOOKUP as another compatible option when the County column is first in the lookup table.
Look up county fees in WPS Spreadsheets
Use a two-column lookup range and an exact-match VLOOKUP formula in WPS Spreadsheets to replace a long nested IF formula.
- 1. Open the workbook: Open WPS Spreadsheets and choose Open to select your workbook.
- 2. Enter the lookup data: Enter County in H1 and Fee in I1, then list up to 120 counties and their fees in H2:I121.
- 3. Add the formula: In the fee cell for the county in A2, enter =IFERROR(VLOOKUP(A2,$H$2:$I$121,2,FALSE),"Not found"). Adjust the range if your list is longer.
- 4. Fill and save: Drag the formula cell's fill handle down, check the returned fees, and choose File > Save.

Frequently Asked Questions
Why should I use a lookup table instead of 120 nested IF statements?
Excel allows up to 64 levels of nested functions. A lookup table avoids that limit and lets you update fees in cells instead of editing a long formula.
Can I use XLOOKUP in Excel 2016?
No. In Excel 2016, use INDEX with MATCH or an exact-match VLOOKUP instead.
Why does the formula return Not found for a county?
Check that the name in A2 matches an entry in the County column and has no extra spaces. Also verify the lookup range or table name and column headers.
What happens if a county appears twice in the lookup table?
XLOOKUP and the exact-match VLOOKUP shown here return the first matching fee. Keep one row per county unless that behavior is intended.




