Function Problems

Return an Excel Mileage Rate Based on a Date or Year

Maira Mehtab Sep 27, 2026 870 views

Question details

The user needs to retrieve a specific mileage rate from a reference table based on a given date or year.

Product: Excel
Device & OS: not provided
Scenario: Calculating travel expenses or mileage reimbursements where rates change periodically depending on the year or exact date.
Observed behavior: The goal is to accurately match a date or year to a reference table using an approximate VLOOKUP formula without returning errors.

Before you start

Ensure your reference table containing the lookup dates or years is sorted in ascending order, as this is mandatory for an approximate VLOOKUP match to function correctly.

Solution 1Recommended

Use VLOOKUP with YEAR Function for Year-Based Matching

Use this method when your main data contains full dates, but your reference table only lists the years.

By combining VLOOKUP with the YEAR function, Excel can extract the year from a full date and match it against a simplified year-based reference table.

Select the destination cell

Click on the cell where you want the calculated mileage rate to appear.

Enter the VLOOKUP formula

Type the formula =IFERROR(VLOOKUP(YEAR(A2),$L$1:$O$5,3,TRUE),0), assuming your date is in cell A2 and the reference table is located in the range L1:O5.

Apply to the column

Press Enter to get the result, then click and drag the fill handle at the bottom-right corner of the cell to copy the formula down to the rest of the column.

Understanding the Formula: The TRUE argument allows an approximate match, finding the closest year without going over. The IFERROR function ensures a 0 is returned instead of an error if no match is found.

Solution 2

Use VLOOKUP for Direct Date Matching

Use this formula when both your input data and reference table contain full dates.

Efficient Data Calculation with WPS Office

Calculate Mileage Rates Easily in WPS Spreadsheet

WPS Spreadsheet fully supports advanced lookup formulas like VLOOKUP, YEAR, and IFERROR, allowing you to calculate mileage reimbursements and manage financial tables effortlessly.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your mileage data.
2. Verify table sorting: Ensure the first column of your lookup table is sorted in ascending order.
3. Enter the formula: Select the target cell and enter your VLOOKUP formula just as you would in Excel.
4. Retrieve rates instantly: Press Enter to instantly retrieve the correct mileage rate and drag down to apply it to multiple entries.

Fully compatible with Microsoft Excel formulas and date functions.Built-in error checking for complex data lookups.Free, lightweight, and user-friendly interface for managing expense sheets.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VLOOKUP formula return the wrong mileage rate?

This usually happens if the lookup column (years or dates) in your reference table is not sorted in ascending order. Sorting in ascending order is required when the range_lookup argument is set to TRUE (approximate match).

Can I use an exact match instead of an approximate match?

Yes. If you only want to return a rate when the exact date or year is found, change the last argument of the VLOOKUP formula from TRUE to FALSE. Note that this will return an error if the exact date is not listed in the table.

How do I handle missing dates in my reference table without showing errors?

Wrapping your VLOOKUP formula in an IFERROR function (e.g., =IFERROR(VLOOKUP(...), 0)) allows you to output a default value like 0 or a blank cell ("") instead of a #N/A error when a match isn't found.

Excel & Data Problems