Return the Current Driver Load Route in WPS Spreadsheets with Lookup Formulas
Question details
Return the route number for a driver's current load from multiple load reports connected to one source data table.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A dispatcher or report owner needs to identify the current route using today's date and, when needed, additional criteria such as driver name or load status.
- Observed behavior
- A lookup formula should display the matching route number and remain blank when no current load matches the selected criteria.
Place all load records in one consistent source table with columns for Load Date, Route, Driver, and Status. Make sure the Load Date values are real spreadsheet dates rather than text and that route and driver entries use consistent names.
Use INDEX and MATCH to return a route for today's load
Use this method when one load record exists for each date and you only need to return its route number.
This formula searches the load-date column for the current date and returns the related route from the route column. IFERROR keeps the result cell empty when there is no matching load for today.
In the source sheet, keep load dates in A5:A34 and the corresponding route numbers in B5:B34. Confirm the dates are formatted as dates by selecting the cells and using Home > Number Format.
Select the report cell where the route should appear, enter =IFERROR(INDEX($B$5:$B$34,MATCH(TODAY(),$A$5:$A$34,0)),"") , and press Enter.
Compare the result with the row dated today in the source data. If no row has today's date, the formula returns a blank cell.
Return the current route by date, driver, and load status
Use this method when several loads can share the same date and the report must show a specific driver's active route.
Find a Driver's Current Route from a Central Loads Table in WPS Spreadsheets
WPS Spreadsheets lets you build a central Loads table and use INDEX, MATCH, TODAY, and multiple criteria to display the route assigned to the current driver load. Update the source records, and linked report formulas recalculate automatically.
- 1. Create a central Loads sheet: Open WPS Spreadsheets and create columns for Load Date, Route, Driver, Status, Origin, and Destination on one source sheet.
- 2. Add the report criteria: On the report sheet, enter the driver name in a criteria cell such as H2 and choose the status value that identifies an active load.
- 3. Insert the lookup formula: Select the route output cell and use INDEX with MATCH to find the row matching TODAY(), the selected driver, and the Current status.
- 4. Fill linked report cells: Copy or adapt the formula for related fields such as load number, origin, destination, and delivery date using their respective return columns.
Frequently Asked Questions
Why does my current-load lookup return a blank cell?
The IFERROR wrapper returns a blank when no source row exactly matches the criteria. Check that the Load Date is a real date, the driver name matches H2 exactly, and the Status column contains the expected value, such as Current.
How can I return a route for a selected date instead of today's date?
Replace TODAY() in the formula with a cell reference containing the selected date. For example, use $H$3 in place of TODAY() after entering the desired date in H3.
What happens if two loads have the same date and driver?
MATCH returns the first row that meets all supplied conditions. Add another unique criterion, such as load number, pickup time, or origin, inside the MATCH expression to identify one specific load.
Can I return other current-load details with the same formula?
Yes. Keep the MATCH criteria section unchanged and change the INDEX return range to the column you need, such as the load-number, origin, destination, or delivery-date column.
Can WPS Spreadsheets open driver-load reports created in Excel?
Yes. WPS Spreadsheets supports Microsoft Excel .xlsx and .xls files, so you can open the reports and use compatible lookup formulas such as INDEX, MATCH, and IFERROR.
