Google Sheets Formula Help

[Jewells0905]

I am trying to create an interactive sheet, in Cell G2 the user will enter a person's last name.

• cell H2 will be used look up the first occurrence of this last name in column A, find the gender of the person, and display it. The result should be proper-cased, i.e. either "Male" or "Female".

• In cell I2 I am trying to to lookup the first occurrence of the last name in column A, find whether the person survived, and display "Yes" if that person did survive and "No" otherwise.

Full name	PClass	Age	Gender	Survived		Last name	Gender	Survived?
Allen, Miss Elisabeth Walton	1st	29	female	1
Allison, Miss Helen Loraine	1st	2	female	0
Allison, Mr Hudson Joshua Creighton	1st	30	male	0
Allison, Mrs Hudson JC (Bessie Waldo Daniels)	1st	25	female	0

I am currently using formula =INDEX(D2:D1314,(MATCH(G2,A2:A1314))) in cell G2, however it is not giving the correct name ex if I search for Alisson, it will pull for 1, which is Allen, not the 1st occurrence of Allison. I believe that I need to use a wildcard, ex G2*, however when I try this as either G2*, or "G2*" it gives an error, stating not found as it's looking for "g2" instead of the last name followed by the first name. Maybe I need to use the ampersand, I'm not sure.

In cell H2, I am currently using =INDEX(D2:D1314,(MATCH(G2,A2:A1314))), but again it is not pulling the correct info as it's going from the 1 position not the 2 position.

 

[Cubist]

H2 Formula:

=PROPER(XLOOKUP("*" & G2 & "*",$A$2:$A$5,$D$2:$D$5,"not found",2,1))

I2 Formula:

=IF(XLOOKUP("*" & G2 & "*",$A$2:A5,$E$2:$E$5,"not found",2,1),"Yes","No")

 

[pjmorris]

I wonder if the first problem is imply the type of MATCH you're using. Try

MATCH(G2,A2:A1314, 0)

which would require an exact match in your G2 formula; the '0' defines the type of search.

 

[Cubist]

Looking at this further, you shouldn't be using a wildcard match because it will also return the person with the first name or middle that matches in cell G2. I'd recommend using a helper column to extract the last name, and then look up the exact match.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Full name	LastName	PClass	Age	Gender	Survived		Last name	Gender	Survived?
2	Allen, Miss Elisabeth Walton	Allen	1st	29	female	1		Allen	Female	Yes
3	Allison, Miss Helen Loraine	Allison	1st	2	female	0
4	Allison, Mr Hudson Joshua Creighton	Allison	1st	30	male	0
5	Allison, Mrs Hudson JC (Bessie Waldo Daniels)	Allison	1st	25	female	0
Sheet6

Cell Formulas
Range	Formula
I2	I2	=PROPER(XLOOKUP(H2,$B$2:$B$5,$E$2:$E$5))
J2	J2	=IF(XLOOKUP(H2,$B$2:$B$5,$F$2:$F$5),"Yes","No")
B2:B5	B2	=LEFT(A2, FIND(",", A2) - 1)

 

[Jewells0905]

Thank you everyone for you help.

Cubist formulas worked.​

 

[Jewells0905]

I wonder if the first problem is imply the type of MATCH you're using. Try

MATCH(G2,A2:A1314, 0)

which would require an exact match in your G2 formula; the '0' defines the type of search.

I had thought that as well, however when I add the 0 in there, it gives me a #N/A error- Did not find value 'Riordan' in MATCH evaluation. I think that might have been because I needed to do some sort of wildcard for the search cell G2, but I wasn't able to figure out how to make that work