Find a Full Name by Matching Last Name and Location in Excel
Question details
Find and return a full name when the name contains a specified last name and its associated location exactly matches another criterion.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user needs a two-condition lookup using a partial text match for a last name and an exact match for a location.
- Observed behavior
- VLOOKUP is not suitable because it does not reliably handle the required partial-name and exact-location criteria together.
Confirm that full names are in A1:A11, locations are in B1:B11, the last-name search term is in G1, and the location criterion is in H1. Remove unintended leading or trailing spaces from location values if exact matches do not return results.
Use FILTER to return every matching full name in Microsoft 365
Use this option when you have Microsoft 365 and want a dynamic list of all full names that match both the last name and location.
FILTER checks both conditions at once: SEARCH finds the entered last name anywhere in each full name, and the location test requires an exact match.
Click the empty cell where you want the matching name or names to appear, such as I1.
Type =FILTER(A1:A11,(ISNUMBER(SEARCH(G1,A1:A11)))*(B1:B11=H1),"No match") and press Enter.
If more than one record matches, Excel spills the matching full names into the cells below the formula. Clear any cells blocking the spill range if a spill error appears.
Use INDEX and MATCH to return the first matching full name
Use this option when only the first matching name is needed or when FILTER is unavailable.
Match Full Names by Last Name and Location in WPS Spreadsheets
WPS Spreadsheets lets you build the same two-condition lookup with familiar Excel-style formulas. Use FILTER for a dynamic list of matching names, or INDEX and MATCH when you need the first matching record.
- 1. Open the worksheet in WPS Spreadsheets: Open the .xlsx or .xls file in WPS Office and verify that names are in column A and locations are in column B.
- 2. Set the lookup criteria: Enter the last name or partial name in G1 and the required location in H1.
- 3. Return all matches with FILTER: In an empty result cell, enter =FILTER(A1:A11,(ISNUMBER(SEARCH(G1,A1:A11)))*(B1:B11=H1),"No match") and press Enter.
- 4. Return one match with INDEX and MATCH: If you need only the first result, enter =INDEX(A1:A11,MATCH(1,(ISNUMBER(SEARCH(G1,A1:A11)))*(B1:B11=H1),0)) in the output cell.
Frequently Asked Questions
Why does VLOOKUP not work well for a partial last name and location lookup?
VLOOKUP is designed around a single lookup value in the first column of a table. The FILTER or INDEX and MATCH formulas can test whether A1:A11 contains the text in G1 while also requiring B1:B11 to equal H1.
How do I return all people with the same last name in one location?
Use =FILTER(A1:A11,(ISNUMBER(SEARCH(G1,A1:A11)))*(B1:B11=H1),"No match"). It returns every full name in A1:A11 whose name contains G1 and whose location in B1:B11 equals H1.
What should I use if I need only one matching full name?
Use =INDEX(A1:A11,MATCH(1,(ISNUMBER(SEARCH(G1,A1:A11)))*(B1:B11=H1),0)). MATCH finds the first row satisfying both criteria, and INDEX returns the name from that row.
Why does the location condition return no match when the locations look identical?
The comparison B1:B11=H1 requires an exact text match. Check for extra spaces, different abbreviations, or inconsistent spelling in the location cells and in H1.
Does SEARCH distinguish between uppercase and lowercase last names?
No. SEARCH is case-insensitive, so a value such as "Smith" in G1 can match "SMITH", "Smith", or "smith" within the full names in A1:A11.
