Function Problems

Match Multiple Criteria for Differently Arranged Data in Excel

Camila MilosovichCamila Milosovich Nov 11, 2026 869 views

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.

How to Look Up and Match Differently Arranged Data in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Select Destination Cell

Click on the first cell in your destination matrix where the data result should appear.

2
Enter Array Formula

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.

3
Fill the Matrix

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.

Use INDEX and XMATCH for Multi-Criteria Lookups
Important: Ensure your absolute and relative cell references (using the $ symbol) are correct before dragging the formula across the matrix, or the criteria ranges will shift.
Advanced Data Processing

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. 1. Open Your Data: Launch WPS Spreadsheet and open your differently arranged dataset.
  2. 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. 3. Generate PivotTables: Alternatively, highlight your data and select Insert > PivotTable to instantly restructure the entire layout without manual formulas.
  4. 4. Save Seamlessly: Save your completed data mapping securely in the universal .xlsx format.
Fully compatible with Microsoft Excel (.xlsx) formats and formulas.Natively supports complex functions like XLOOKUP, INDEX, and MATCH.Free to use, lightweight, and fast even when analyzing massive datasets.
microsoft office alternative - wps office

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.