How to Filter Rows by Matching First and Last Name in Excel
Question details
Extract specific rows from a dataset where the combination of 'First Name' and 'Last Name' columns perfectly matches the value in the 'Primary Contact' column.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Filtering a dataset to find records with consistent contact information across multiple columns.
- Observed behavior
- The user requires an array formula that dynamically returns only the rows satisfying the combined name matching condition.
Ensure your data is formatted as an Excel Table named 'Sample' to use structured references, or be prepared to update the formula to match your actual cell ranges (e.g., A2:A100).
Filter Data Using the LET and FILTER Functions
Leverage dynamic array functions to create a temporary variable for the combined name check, then filter the dataset to display only the matching rows.
This method combines the First Name and Last Name with a space character in between, compares it to the Primary Contact column, and returns the entire row if they match. Using the LET function makes the formula easier to read by defining the logical test as a variable called 'check'.
Click on a blank cell where you want the filtered data to begin appearing. Make sure there is enough empty space below and to the right.
Type the formula: =LET(check, Sample[First Name] & " " & Sample[Last Name] = Sample[Primary Contact], FILTER(Sample, check))
Press Enter. The formula will automatically spill the extracted rows into the adjacent cells, returning only the records where the names match.
Filter Data Using the FILTER Function Directly
If you prefer a shorter formula without defining variables, you can nest the comparison condition directly inside the FILTER function.
Filter Complex Datasets Seamlessly with WPS Spreadsheet
WPS Spreadsheet offers powerful dynamic array capabilities, allowing you to use advanced functions like FILTER and LET to manipulate and extract data exactly like Microsoft Excel.
- 1. Open your data in WPS: Launch WPS Spreadsheet and open the file containing your contact data.
- 2. Select the output cell: Click on a blank cell where you want the filtered results to be displayed.
- 3. Enter the formula: Type =FILTER(Sample, Sample[First Name]&" "&Sample[Last Name]=Sample[Primary Contact]).
- 4. Generate the results: Press Enter to generate the filtered list dynamically.
Frequently Asked Questions
Why is my FILTER formula returning a #CALC! error?
This error occurs when the FILTER function finds no rows that meet the specified criteria. You can prevent this by adding the optional 'if_empty' argument to your formula, such as: =FILTER(Sample, check, "No matches found").
How do I handle extra spaces in the first or last name columns?
Accidental spaces can cause the match to fail. You can wrap your column references in the TRIM function to remove leading or trailing spaces before comparing them: TRIM(Sample[First Name]) & " " & TRIM(Sample[Last Name]).
Is the name comparison in this formula case-sensitive?
No, the standard equals (=) operator in Excel and WPS Spreadsheet is not case-sensitive, meaning 'John Doe' will successfully match 'john doe'. If you need strict case matching, you must use the EXACT function instead of the equals sign.
