Use Trim while doing and Index Search Match. Simple formula 4 a complex task ?

[fasullo]

Hi,
I have the below excel file in dropbox. promise it's clean

I would like a simple formula for a complex task. Do not want to add any columns or sort any data. The data will not be sorted alphabetically in any tab.
1) worksheet "email" has a person's full name in column A (with a space before the name). Column B is when the person read an e-mail.
2) worksheet "Client Population" has the first & last name broken out in columns A & B.
3) My goal in column C of worksheet "Client Population" : is to combine columns A & B to match with the full name of column A within worksheet "email" and return data in column B within worksheet "email" when the person read the e-mail.

https://dl.dropboxusercontent.com/u/52990313/Trim Index Sesrch Match.xlsx

I know I can use =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) within worksheet "email" to delete the first space.
I know =CONCATENATE(A3:A3," ",B3) worksheet "Client Population" will combine the name. It's putting these two formulas inside some index lookup to get what I need. The formula does not have to use index, search, match, as long as it works.

Thank-you for your time and help!

 

[Weazel]

probably a couple of different ways to do it but see if this works for you

Excel 2012

	A	B	C	D
Firstname	Lastname	Goal: Return When read e-mail
Jack	Jones
George	Jones
Tim	Smith
Clint	Dod
Dan	Wheeler

<tbody>
[TD="align: center"]1[/TD]

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

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

[TD="align: right"]7/8/2013[/TD]
[TD="align: right"]7/8/2013[/TD]

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

[TD="align: right"][/TD]
[TD="align: right"]8/5/2013[/TD]

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

[TD="align: right"][/TD]
[TD="align: right"]8/9/2013[/TD]

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

[TD="align: right"][/TD]
[TD="align: right"]#N/A[/TD]

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

[TD="align: right"][/TD]
[TD="align: right"]#N/A[/TD]

</tbody>

Client Population​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]{=INDEX(email!$B$2:$B$4,MATCH(A2&" "&B2,TRIM(email!$A$2:$A$4),0))}[/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]

 

[marc j]

There is also the vlookup, (i prefer index and match)

The trick is to concatenate in the formula,

=VLOOKUP(" "&A3&" "&B3,email!$A$2:$B$14,2,0) -just enter-

=VLOOKUP(A3&" "&B3,TRIM(email!$A$2:$B$14),2,0) -ctlr shift enter-

weazel did the concatenate in the match
MATCH(A2&" "&B2,TRIM(email!$A$2:$A$4)

Marc J

 

[fasullo]

I think the vlookup could work, but what if someone has a middle initial. Tom K. Jones ? The first name column would be Tom K.

 

[Weazel]

you could try this if there is a middle initial and you have the middle initial in a separate column on the client population sheet and the value you are looking up is like John J. Jones

though I suppose you could drop the "1*" part of the formula if you just want the date as text

Excel 2012

	A	B	C	D	E
Firstname	Lastname	Goal: Return When read e-mail
Jack	J.	Jones
George	Jones
Tim	A.	Smith
Clint	Dod
Dan	Wheeler

<tbody>
[TD="align: center"]1[/TD]

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

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

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

[TD="align: right"]7/8/2013[/TD]
[TD="align: right"]7/8/2013[/TD]

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

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

[TD="align: right"][/TD]
[TD="align: right"]8/5/2013[/TD]

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

[TD="align: right"][/TD]
[TD="align: right"]8/9/2013[/TD]

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

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

[TD="align: right"][/TD]
[TD="align: right"]#N/A[/TD]

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

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

[TD="align: right"][/TD]
[TD="align: right"]#N/A[/TD]

</tbody>

Client Population​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]{=1*VLOOKUP(A2&B2&C2,SUBSTITUTE(email!$A$2:$B$4," ",""),2,0)}[/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]