Function Problems

Replace Nested IF Statements with an Excel County Fee Lookup Table

Rana GarciaRana Garcia Nov 18, 2026 868 views

Question details

Replace 120 nested IF statements with a maintainable lookup table that returns a fee for each county.

Replace Nested IF Statements with an Excel County Fee Lookup Table
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.
Before you start

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.

Solution 1Recommended

Use XLOOKUP with a county fee table

Use this approach in an Excel version that supports XLOOKUP to make fee updates easy.

1
Create the lookup table

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.

2
Name the table

Click inside the table and use Table Design > Table Name to name it feeTbl.

3
Enter the lookup formula

In the fee cell for the county in A2, enter =XLOOKUP(A2,feeTbl[County],feeTbl[Fee],"Not found").

4
Fill down and check

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 XLOOKUP with a county fee table
Keep county names unique: If a county appears more than once, XLOOKUP returns the first match. Update the Fee column whenever a rate changes.
Work with county fee tables in WPS

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. 1. Open the workbook: Open WPS Spreadsheets and choose Open to select your workbook.
  2. 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. 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. 4. Fill and save: Drag the formula cell's fill handle down, check the returned fees, and choose File > Save.
Update county fees directly in the lookup range.Use exact-match VLOOKUP instead of a deeply nested formula.Open and edit Microsoft Excel XLSX and XLS files.Work in a familiar spreadsheet interface.
microsoft office alternative - wps office

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.