How to Match Data Between Two Excel Worksheets (Formulas & VBA)
Question details
The user needs to match records between two Excel worksheets using multiple criteria (like number, name, and class) to return corresponding assignment or score values.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Comparing and extracting related data from a secondary worksheet based on matching specific identifiers.
- Observed behavior
- Successfully retrieving assignment or score values from a secondary sheet into the primary sheet when multiple conditions are met.
Ensure both worksheets are in the same workbook for easier referencing, and verify that your matching columns (like names or IDs) have no leading or trailing spaces.
Use INDEX and MATCH for Multiple Criteria
This is the most robust method for matching data when you need to check two or more conditions (e.g., matching both Number and Name).
Using a combination of INDEX and MATCH allows Excel to evaluate multiple arrays and return a value only when all criteria are satisfied. This avoids the limitations of basic VLOOKUP.
Click on the first empty cell in your target column on Sheet1 (e.g., C2).
Type the formula: =IFERROR(INDEX(Sheet2!$C$2:$E$10000,MATCH(1,INDEX((Sheet2!$A$2:$A$100000=$A2)*(Sheet2!$B$2:$B$100000=$B2),,),0),COLUMN()-2),"NA")
Press Enter to evaluate the array formula. If using older versions of Excel, you may need to press Ctrl+Shift+Enter.
Click and drag the fill handle at the bottom-right of the cell to the right, and then double-click it to fill the formula down your entire dataset.
Use VLOOKUP for a Single Match Condition
If you only need to match data using a single unique identifier, VLOOKUP provides a simpler and faster formula.
Match Data Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced array formulas like INDEX and MATCH, as well as VLOOKUP and XLOOKUP, allowing you to accurately cross-reference large datasets without lagging.
- 1. Open your file: Launch WPS Spreadsheet and open the workbook containing your datasets.
- 2. Select the target cell: Navigate to the exact cell where you want the matched data to appear.
- 3. Input the formula: Type your INDEX/MATCH or VLOOKUP formula exactly as you would in Excel.
- 4. Apply across rows: Press Enter, then drag the fill handle to seamlessly apply the matching logic across your rows.
Frequently Asked Questions
Why is my INDEX MATCH formula returning an #N/A error?
Common reasons include mismatched data types (e.g., numbers stored as text in one sheet but not the other), trailing spaces, or unequal array sizes in your formula. Double-check that your lookup ranges (like $A$2:$A$10000) are the exact same size.
Can I use XLOOKUP to match multiple criteria instead?
Yes, if your spreadsheet software supports it, you can use XLOOKUP by concatenating the lookup values and lookup arrays using the '&' symbol (e.g., XLOOKUP(A2&B2, Sheet2!A:A&Sheet2!B:B, Sheet2!C:C)), which is often easier to read than a complex INDEX MATCH.
How do I match data across two completely separate workbooks?
You can reference a different workbook by keeping both files open and clicking the target range in the external file while writing your formula. The formula will automatically include the external file name enclosed in square brackets.
