Function Problems

How to Look Up and Interpolate Rainfall Values in Excel

John WilsonJohn Wilson Nov 11, 2026 868 views

Question details

The user needs to find rainfall intensity values for specific times not explicitly listed in a data table by looking up adjacent values and applying linear interpolation across multiple recurrence intervals.

How to Look Up and Interpolate Rainfall Values in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
Calculating interpolated rainfall intensity for a given Time of Concentration (Tc) across 2-year to 100-year recurrence intervals.
Observed behavior
To get the exact value, the user must extract the nearest lower and upper times from a source table and calculate the intermediate rainfall values using lookup formulas.
Before you start

Ensure your rainfall intensity data table is sorted in ascending order by time so that lookup functions like MATCH and XLOOKUP can accurately identify the nearest lower and upper bounds.

Solution 1Recommended

Use XLOOKUP to Find Nearest Values (Microsoft 365)

For users on Microsoft 365, XLOOKUP provides a clean and dynamic way to extract the next higher or lower values for interpolation.

XLOOKUP is a powerful function that allows for approximate matching and can return an entire array of data at once. This is highly useful when filling out 2-year through 100-year values based on a single lookup value.

1
Select the destination cell

Click the cell where you want the interpolated values to appear for the upper boundary.

2
Enter the XLOOKUP formula

Use the XLOOKUP function to find the exact or next larger item. Type: =TRANSPOSE(XLOOKUP(G3,'Riley County Rainfall Intensity'!A2:A117,'Riley County Rainfall Intensity'!B2:G117,"Not found",1))

3
Allow the array to spill

Press Enter to allow the dynamic array to spill the values across the adjacent cells for the different recurrence intervals.

4
Apply linear interpolation

Combine these extracted upper and lower boundaries with a standard linear interpolation formula (y = y1 + ((x - x1) / (x2 - x1)) * (y2 - y1)) to calculate the exact rainfall intensity.

Use XLOOKUP to Find Nearest Values (Microsoft 365)
Dynamic Arrays: Because XLOOKUP supports dynamic arrays, you do not need to press Ctrl+Shift+Enter; pressing Enter is sufficient.
Advanced Spreadsheet Alternative

Effortlessly Look Up Data with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array formulas like XLOOKUP, INDEX, and MATCH, allowing you to easily look up data and perform linear interpolation without any compatibility issues.

  1. 1. Open your data workbook: Launch WPS Spreadsheet and open the file containing your rainfall data table.
  2. 2. Apply lookup formulas: Use =XLOOKUP() or =INDEX(MATCH()) to easily locate your upper and lower data boundaries.
  3. 3. Perform interpolation: Apply standard interpolation math formulas across the cells to find your exact value.
  4. 4. Save your work: Save your workbook seamlessly in the standard Microsoft Excel .xlsx format.
Fully compatible with Microsoft Excel formulas and standard .xlsx formats.Supports dynamic arrays and advanced lookup functions natively.Lightweight software that processes large data tables and interpolations quickly.Free to use with a familiar, tab-style user interface.
microsoft office alternative - wps office

Frequently Asked Questions

Why does my MATCH function return an error when looking up the time?

MATCH requires the lookup array to be sorted in ascending order when using a match_type of 1 (less than). Ensure your time column is sorted from smallest to largest to avoid #N/A errors.

Can I perform linear interpolation without helper cells?

Yes, you can combine the lower and upper lookup formulas directly into a single interpolation formula. Using the LET function allows you to define the lookup variables first, keeping the overall formula clean and readable.

Why do I need to press Ctrl+Shift+Enter for the INDEX/MATCH formula?

In older versions of Excel that do not support dynamic arrays natively, pressing Ctrl+Shift+Enter forces the program to evaluate the formula as an array formula, properly returning the results for multiple recurrence intervals at once.