Multple Column Search - 1x text within text search + match - excel formula

[Froggy74]

Hi All,
I am struggling and need the brains of this forum to help me please.

I am looking for an excel formula that can search multiple columns in a range of data and return an answer in the third column. The difficulty I am having is 1 criteria requires searching for specific text in an email address (so not the whole email) and then combine with their LAST Name to return a user code.
I have provided an example data set (the actual data set is 50,000 lines long)

Code LAST FIRST EMAIL
ABC Doe Jon jondoe@xyz.us
BBC Smith Granny snowysmith@xyz.us
CBC Day Happy snowy.a.day@xyz.us

The criteria I have is a user called "Snowy" last name "Day". However their formal first name is not "Snowy" but "Happy". What I am trying to return is Code CBC.

I am looking for an excel formula (akin to an array index match formula) that can match "snowy" from the email and Day from the Last name to give me the CBC code.

Can anyone help?

 

[Eric W]

Welcome to the Board!

Try:

Excel 2012

	A	B	C	D	E	F	G	H
Code	LAST	FIRST	EMAIL	Last Name	Email part
ABC	Doe	Jon	jondoe@xyz.us	Day	snowy
BBC	Smith	Granny	snowysmith@xyz.us
CBC	Day	Happy	snowy.a.day@xyz.us	# of matches	First match
CBC

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G5[/TH]
[TD="align: left"]=COUNTIFS(B:B,G2,D:D,"*"&H2&"*")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H5[/TH]
[TD="align: left"]{=IFERROR(INDEX(A:A,SMALL(IF(B2:B100=G2,IF(ISNUMBER(SEARCH(H2,D2:D100)),ROW(B2:B100))),1)),"no match")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[Froggy74]

Eric W. you are a magician!

 

[Eric W]

Glad it works for you!