How to Look Up and Interpolate Rainfall Values in Excel
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.

- 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.
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.
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.
Click the cell where you want the interpolated values to appear for the upper boundary.
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))
Press Enter to allow the dynamic array to spill the values across the adjacent cells for the different recurrence intervals.
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 INDEX and MATCH for Older Excel Versions
If you are using Excel 2019 or older, you can combine INDEX and MATCH to locate the boundary values.
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. Open your data workbook: Launch WPS Spreadsheet and open the file containing your rainfall data table.
- 2. Apply lookup formulas: Use =XLOOKUP() or =INDEX(MATCH()) to easily locate your upper and lower data boundaries.
- 3. Perform interpolation: Apply standard interpolation math formulas across the cells to find your exact value.
- 4. Save your work: Save your workbook seamlessly in the standard Microsoft Excel .xlsx format.

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.




