Match Multiple Criteria for Differently Arranged Data in Excel
Question details
The user needs to retrieve specific values (such as mileage) from a differently arranged worksheet by matching multiple criteria, including date and fleet numbers.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Consolidating and mapping data across worksheets where the source data and destination layout do not match, requiring a multi-condition lookup.
- Observed behavior
- The user requires an efficient way to return exact values across a large matrix without manually reorganizing the original source columns.
Ensure that your source and destination datasets use identical data types for the lookup criteria. For example, dates must be stored as actual date values rather than text strings to ensure accurate matching.
Use INDEX and XMATCH for Multi-Criteria Lookups
This method handles multiple conditions simultaneously using boolean logic to find exact row matches, eliminating the need for auxiliary helper columns.
By multiplying conditions together, you force Excel to create an internal array of 1s (where all conditions are met) and 0s (where they fail). XMATCH then effortlessly locates the row returning a 1.
Click on the first cell in your destination matrix where the data result should appear.
Type the formula combining INDEX and XMATCH, for example: =IFERROR(INDEX('Source'!$F$3:$F$1188, XMATCH(1, ('Source'!$A$3:$A$1188=$A3)*('Source'!$D$3:$D$1188=E$1))), ""). Adjust the ranges as necessary.
Press Enter to apply the formula, then click and drag the fill handle to the right and down to populate the rest of your table.

Apply Dynamic Array Functions (XLOOKUP and MAKEARRAY)
If you are using Microsoft 365, combining dynamic array functions allows you to populate an entire table from a single cell.
Restructure Source Data Using a PivotTable
If you simply need to summarize the data into a matrix layout (like Fleet vs. Month), using a PivotTable is significantly faster and requires zero complex array formulas.
Handle Complex Lookups Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced array formulas, multi-criteria lookups, and dynamic PivotTables. It is designed to help you reorganize and manage differently arranged data seamlessly.
- 1. Open Your Data: Launch WPS Spreadsheet and open your differently arranged dataset.
- 2. Use Lookup Formulas: Navigate to the Formulas tab to quickly insert your preferred advanced lookup functions, such as XLOOKUP or INDEX and MATCH.
- 3. Generate PivotTables: Alternatively, highlight your data and select Insert > PivotTable to instantly restructure the entire layout without manual formulas.
- 4. Save Seamlessly: Save your completed data mapping securely in the universal .xlsx format.

Frequently Asked Questions
Can I use VLOOKUP to match multiple criteria across worksheets?
Standard VLOOKUP only supports a single lookup value natively. To use VLOOKUP for multiple criteria, you must create a helper column in your source data that concatenates the conditions together (e.g., =A2&B2). For a cleaner approach without altering source data, use XLOOKUP or INDEX/MATCH.
Why is my XMATCH array formula returning a #VALUE! error?
This error typically occurs if the ranges for your criteria arrays are not exactly the same size. Ensure that all ranges referenced in your formula (e.g., $A$3:$A$1188 and $B$3:$B$1188) encompass the exact same number of rows.
How does boolean logic work in multiple-criteria lookups?
By multiplying logical conditions together—like (Range1=Condition1)*(Range2=Condition2)—Excel evaluates each cell to either TRUE (1) or FALSE (0). The multiplication combines them into an array of 1s and 0s, where a 1 means all criteria are met simultaneously.




