Find last name with just knowing the first name

[Ezguy4u]

The problem is you know the first name but forgot the last name. So, you type the first name type the first name in C6 and some function will look at the list of names and the output is a single name or few people with the same first name. I would like to use functions from 365

Arnold Question.xlsx
	A	B	C	D
1	Name
2	Paul Wells
3	Gary Miller
4	James Willard
5	Paul Garza		Type first name	output
6	Richard Elliot		Paul	Paul Wells
7	Robert Spear			Paul Garza
8	Roger Mun			Paul Hill
9	Paul Hill
Data

 

[Eric W]

Try:

Book1
	A	B	C	D
1	Name
2	Paul Wells
3	Gary Miller
4	James Willard
5	Paul Garza		Type first name	output
6	Richard Elliot		Paul	Paul Wells
7	Robert Spear			Paul Garza
8	Roger Mun			Paul Hill
9	Paul Hill
Sheet5

Cell Formulas
Range	Formula
D6:D8	D6	=FILTER(A2:A9,LEFT(A2:A9,LEN(C6))=C6)
Dynamic array formulas.

Note that this would also return "Pauline Smith". Name matching is actually quite tricky with different spellings, spaces, punctuation, etc. But this should give you a basic list. Let us know how it works.

 

[Peter_SSs]

As @Eric W has indicated, the formula could be problematic for Paul/Pauline and would be more so if the fist name was "Jo" as you could also have "Joe", "Joseph", "Josephine", "Jolene", "John", "Jock", "Joyce" etc.
A simple adjustment to Eric's formula should resolve that. Eric's formula in col D, new formula in col E

24 03 17.xlsm
	A	B	C	D	E
1	Name
2	Paul Wells
3	Gary Miller
4	James Willard
5	Paul Garza		Type first name	output	output
6	Richard Elliot		Paul	Paul Wells	Paul Wells
7	Pauline Spear			Paul Garza	Paul Garza
8	Roger Mun			Pauline Spear	Paul Hill
9	Paul Hill			Paul Hill
10
Filter Names

Cell Formulas
Range	Formula
D6:D9	D6	=FILTER(A2:A9,LEFT(A2:A9,LEN(C6))=C6)
E6:E8	E6	=FILTER(A2:A9,LEFT(A2:A9,LEN(C6)+1)=C6&" ")
Dynamic array formulas.