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
1	Firstname	Lastname	Goal: Return When read e-mail
2	Jack	Jones	7/8/2013	7/8/2013
3	George	Jones		8/5/2013
4	Tim	Smith		8/9/2013
5	Clint	Dod		#N/A
6	Dan	Wheeler		#N/A

<tbody>
</tbody>

Client Population​

Array Formulas Cell Formula D2 {=INDEX(email!$B$2:$B$4,MATCH(A2&" "&B2,TRIM(email!$A$2:$A$4),0))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[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
1	Firstname		Lastname	Goal: Return When read e-mail
2	Jack	J.	Jones	7/8/2013	7/8/2013
3	George		Jones		8/5/2013
4	Tim	A.	Smith		8/9/2013
5	Clint		Dod		#N/A
6	Dan		Wheeler		#N/A

<tbody>
</tbody>

Client Population​

Array Formulas Cell Formula E2 {=1*VLOOKUP(A2&B2&C2,SUBSTITUTE(email!$A$2:$B$4," ",""),2,0)} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>