IF Statement the return cell value

[lemonap618]

Greetings all,
I'm struggling with an IF statement.
I need it to do the following:

• take the last name (last text string) in cell i2
• take that value and look in column E
• if the value is contained in column E, return the value from the cell

Please feel free to ask questions if I'm being vague or hard to follow.

Much thanks,
Andrew

 

[dermie_72]

you could try:
=IF(COUNTIF(E:E,RIGHT(I2,(LEN(I2)-SEARCH(" ",I2,1))))>0,INDEX(E:E,MATCH(RIGHT(I2,(LEN(I2)-SEARCH(" ",I2,1))),E:E,0),0),"")

 

[lemonap618]

HHmmm. I get a blank cell.
To clarify, I have a list of salespeople's first and last name in column "i". I have e-mail addresses in column "e". I need an IF statement to pull the last name of the salesperson in column "i" and return the e-mail address found in column "e" if the e-mail address contains the last name of the salesperson.

I hope I'm not being too confusing.
Cheers,
Andrew