Multiple criteria lookup including partial text match

[Samosa]

Hello,

I'm trying to match cases from my lookup list to my master list below using multiple criteria, to get the output in column H. I am able to match the "Chris Dobbs" case in row 4 by concatenating last name and gender on each of the two lists. But i cant figure out how I would find "Greg Hamm" given the first name is Greg in the Associate list and Greg B in the master list.

Thanks in advance for your help,
Roy

[TABLE="width: 576"]
<tbody>[TR]
[TD="class: xl111, width: 64"][/TD]
[TD="class: xl111, width: 64"]A[/TD]
[TD="class: xl111, width: 64"]B[/TD]
[TD="class: xl111, width: 64"]C[/TD]
[TD="class: xl111, width: 64"]D[/TD]
[TD="class: xl111, width: 64"]E[/TD]
[TD="class: xl111, width: 64"]F[/TD]
[TD="class: xl111, width: 64"]G[/TD]
[TD="class: xl111, width: 64"]H[/TD]
[/TR]
[TR]
[TD="class: xl111, align: right"]1[/TD]
[TD="class: xl110, colspan: 2"]ASSOCIATE list[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl110, colspan: 2"]MASTER LIST[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl111, align: right"]2[/TD]
[TD]FIRST[/TD]
[TD]LAST[/TD]
[TD]GENDER[/TD]
[TD][/TD]
[TD]FIRST[/TD]
[TD]LAST[/TD]
[TD]GENDER[/TD]
[TD]Associate?[/TD]
[/TR]
[TR]
[TD="class: xl111, align: right"]3[/TD]
[TD]Al[/TD]
[TD]Baker[/TD]
[TD]M[/TD]
[TD][/TD]
[TD]Al B[/TD]
[TD]Baker[/TD]
[TD]F[/TD]
[TD="class: xl112"]No[/TD]
[/TR]
[TR]
[TD="class: xl111, align: right"]4[/TD]
[TD]Chris[/TD]
[TD]Dobbs[/TD]
[TD]M[/TD]
[TD][/TD]
[TD]Chris[/TD]
[TD]Dobbs[/TD]
[TD]M[/TD]
[TD="class: xl112"]Yes[/TD]
[/TR]
[TR]
[TD="class: xl111, align: right"]5[/TD]
[TD]Ed[/TD]
[TD]Franken[/TD]
[TD]F[/TD]
[TD][/TD]
[TD]Ed R[/TD]
[TD]Franken[/TD]
[TD]F[/TD]
[TD="class: xl112"]Yes[/TD]
[/TR]
[TR]
[TD="class: xl111, align: right"]6[/TD]
[TD]Greg[/TD]
[TD]Hamm[/TD]
[TD]F[/TD]
[TD][/TD]
[TD]Greg B[/TD]
[TD]Hamm[/TD]
[TD]F[/TD]
[TD="class: xl112"]Yes[/TD]
[/TR]
[TR]
[TD="class: xl111, align: right"]7[/TD]
[TD]Illene[/TD]
[TD]James[/TD]
[TD]M[/TD]
[TD][/TD]
[TD]Illene[/TD]
[TD]James[/TD]
[TD]M[/TD]
[TD="class: xl112"]Yes[/TD]
[/TR]
[TR]
[TD="class: xl111, align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Kevin[/TD]
[TD]Lamb[/TD]
[TD]M[/TD]
[TD="class: xl112"]No[/TD]
[/TR]
[TR]
[TD="class: xl111, align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mike[/TD]
[TD]Nelson[/TD]
[TD]M[/TD]
[TD="class: xl112"]No[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

Try this, Array Formula to be confirmed by CSE (Control, Shift, Enter):

Book1
	A	B	C	D	E	F	G	H
1	ASSOCIATE list				MASTER LIST
2	FIRST	LAST	GENDER		FIRST	LAST	GENDER	Associate?
3	Al	Baker	M		Al B	Baker	F	No
4	Chris	Dobbs	M		Chris	Dobbs	M	Yes
5	Ed	Franken	F		Ed R	Franken	F	Yes
6	Greg	Hamm	F		Greg B	Hamm	F	Yes
7	Illene	James	M		Illene	James	M	Yes
8					Kevin	Lamb	M	No
9					Mike	Nelson	M	No
Sheet218

Cell Formulas
Range	Formula
H3	{=IF(IFERROR(MATCH(LEFT(E3,FIND(" ",E3&" ")-1)&F3&G3,A$3:A$7&B$3:B$7&C$3:C$7,0),0),"Yes","No")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Formula copied down.