Function Problems

How to Filter the Latest Row by Multiple Criteria in Excel

Kushani Nimanthika Sep 25, 2026 870 views

Question details

The user needs an Excel formula to filter data based on vehicle number and service type, retrieve the most recent record, and display specific columns vertically.

How to Filter the Latest Row by Vehicle and Service Type in Excel

Product: Excel
Device & OS: not provided
Scenario: Filtering and extracting specific columns from the latest matching data record for vehicle maintenance or service logs.
Observed behavior: The user wants to input vehicle and service criteria and dynamically output transposed (vertical) specific columns of the latest matching row, with proper date and number formatting applied.

Before you start

Ensure your spreadsheet application supports Dynamic Array functions like FILTER and CHOOSECOLS, which are available in Microsoft 365, Office 2021, and modern versions of WPS Office.

Solution 1Recommended

Use a combination of FILTER, CHOOSECOLS, MAXIFS, and TRANSPOSE

This single dynamic array formula filters the data set for the specified conditions, finds the latest date, selects exact columns, and transposes them vertically.

By nesting MAXIFS inside a FILTER function, you can identify the most recent record that matches both the vehicle number and the service type. Wrapping this in CHOOSECOLS allows you to pick specific data points, and TRANSPOSE flips the final horizontal row into a vertical list.

Prepare your criteria cells

Identify the cells where you will enter your filter criteria. For this example, assume cell O16 contains the target vehicle number and cell O17 contains the desired service type.

Enter the nested dynamic array formula

Select the destination cell (e.g., N18) where you want the vertical data to begin. Type the following formula and press Enter: =TRANSPOSE(CHOOSECOLS(FILTER(A3:J14,(A3:A14=O16)*(D3:D14=O17)*(B3:B14=MAXIFS(B3:B14,A3:A14,O16,D3:D14,O17))),2,9,5,7))

Format the resulting output cells

Because the formula transposes specific columns into rows, you must format each resulting cell manually to match the data type. Right-click the date cell (e.g., N18), select 'Format Cells', and apply a Date format. Right-click the numerical cell (e.g., N21) and apply a Number format with a thousands separator.

Use a combination of FILTER, CHOOSECOLS, MAXIFS, and TRANSPOSE

Function Breakdown: The CHOOSECOLS function here extracts the 2nd, 9th, 5th, and 7th columns from the matched row in the A3:J14 range. Adjust these numbers to extract different columns based on your specific dataset.

Perform Advanced Data Filtering Easily with WPS Office

WPS Office Spreadsheets fully supports advanced dynamic array functions like FILTER, CHOOSECOLS, and MAXIFS, allowing you to extract and organize complex data seamlessly without requiring extra add-ins.

1. Open your data file in WPS Spreadsheets: Launch WPS Office and open your .xlsx workbook containing the vehicle and service records.
2. Enter the dynamic formula: Select your target output cell and type the combined FILTER, MAXIFS, and TRANSPOSE formula to retrieve the latest row.
3. Apply cell formatting: Select the transposed result cells, press Ctrl+1 to open the Format Cells dialog, and apply the appropriate Date and Number formatting.

Fully compatible with Microsoft Excel file formats (.xlsx, .xls, .csv).Native support for modern dynamic array formulas.Lightweight software with a familiar interface for quick workflow adoption.Completely free to use for daily spreadsheet tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my FILTER formula return a #CALC! error?

A #CALC! error typically occurs if the FILTER function finds no matching records for the specified vehicle and service type. You can add an 'if_empty' argument to the FILTER function, such as FILTER(range, criteria, "No match"), to handle this gracefully.

Can I use index numbers instead of CHOOSECOLS if my Excel version is older?

Yes. If your spreadsheet application does not support CHOOSECOLS, you can use the INDEX function wrapped around your FILTER formula to extract specific columns. However, this requires an array constant for the columns, structured like INDEX(FILTER(...), 1, {2,9,5,7}).

How does the MAXIFS function help in finding the latest row?

The MAXIFS function evaluates the date column based on your criteria (vehicle and service type) and returns the highest date value, which corresponds to the most recent entry. This maximum date is then used as a third condition in the FILTER function to isolate only that latest row.

Excel & Data Problems