How to Match Two Excel Name Lists and Return Scores
Question details
The user needs to identify names that appear in two separate lists and retrieve the corresponding scores from each list into adjacent columns.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Comparing two datasets to extract common entries and align their associated metrics while excluding entries that do not appear in both lists.
- Observed behavior
- A clean summary table is generated containing only the matched names common to both lists, alongside their respective scores retrieved from the original sources.
Ensure both lists are properly aligned with names in one column and scores in the adjacent column. It is highly recommended to remove any trailing spaces or hidden characters in the name columns to prevent false mismatches.
Use Array Formulas and VLOOKUP to Match Lists
This method uses an array formula to dynamically extract names common to both lists, followed by VLOOKUP to fetch the respective scores.
This formula-based approach is highly versatile for smaller datasets. The combination of INDEX, SMALL, IF, and COUNTIF identifies intersecting names, while VLOOKUP aligns the data.
In an empty column, enter the array formula: =INDEX($A$2:$A$12,SMALL(IF(COUNTIF($D$2:$D$12,$A$2:$A$12),MATCH(ROW($A$2:$A$12),ROW($A$2:$A$12)),""),ROWS($A$1:A1))) to find names that exist in both ranges.
Press Ctrl+Shift+Enter to confirm the formula. Then, click and drag the fill handle down to populate the list of matching names until you see errors or blanks indicating no more matches.
In the column next to your matched names (assuming the matched name is in H2), enter the formula =VLOOKUP($H2,$A$2:$B$12,2,FALSE) to pull the score from List 1.
In the subsequent column, enter the formula =VLOOKUP($H2,$D$2:$E$12,2,FALSE) to pull the corresponding score from List 2.
Use Power Query to Merge Data
Ideal for larger datasets, Power Query can perform an inner join to efficiently extract only the matching records without relying on complex formulas.
Match Data Across Sheets Easily with WPS Spreadsheet
WPS Spreadsheet offers powerful data processing capabilities, including advanced array formulas and VLOOKUP functions, allowing you to seamlessly cross-reference large lists and extract matching data effortlessly.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing the two lists you want to match.
- 2. Input the lookup formula: Select your destination cell and type in the VLOOKUP or INDEX/MATCH formula as needed.
- 3. Apply array calculation: If you are extracting a dynamic list of matched names, press Ctrl+Shift+Enter to properly execute the array formula.
- 4. Fill the columns: Drag the fill handle down and across to quickly apply the formula to the rest of the dataset.
Frequently Asked Questions
Why is my VLOOKUP returning #N/A for names that look identical?
This usually happens due to hidden trailing spaces or different text formats. Use the TRIM() function on both name lists to clean the text before attempting to match them.
Can I use XLOOKUP instead of VLOOKUP for this task?
Yes, if your spreadsheet software supports it, XLOOKUP is a robust alternative that doesn't require the lookup value to be in the first column and handles missing data natively without needing IFERROR.
How do I handle duplicate names in the lists?
If a name appears multiple times, VLOOKUP will only return the first match it finds. To handle duplicates accurately, consider assigning unique IDs (like employee numbers) to each person, or use Power Query to group the data before merging.
What happens to the array formula if I add new names to the lists?
You will need to manually update the absolute ranges (e.g., changing $A$2:$A$12 to $A$2:$A$20) in the formula. To avoid this, convert your lists to Excel Tables so the ranges expand automatically when new data is added.
