If cell contains certain text, then return value

[CRISTINA1128]

Hello, I'm working in EXCEL 2016 and trying to create a formula where If cell contains certain text, then return value.

I have the text in column B and would like to create a formula that returns the values in column A.

Any help is greatly appreciated

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]COLUMN A[/TD]
[TD]COLUMN B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]PAK - FC[/TD]
[TD]PAUL[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ANP - FC[/TD]
[TD]ANDY[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]HOU[/TD]
[TD]NALINI[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]SUKHY - UK[/TD]
[TD]SUKHY[/TD]
[/TR]
</tbody>[/TABLE]

 

[cerfani]

=IF(SEARCH("???",B1)>0,A1,"TEXT NOT FOUND")

replace "???" with what you are looking for, you can reference the cell that has the value you are looking for too

you could write that formula in C1 and drag down.

 

[CRISTINA1128]

Thank you for your help - however, Column B has 4 different salesreps (their names are PAUL, ANDY, NALINI and SUKHY). And I want to create a formula that recognizes these names and return a value in Column A (their initials - like what I detailed in Column A).

I could do a search & replace....but I just thought I could create a formula to do this as well.

THANK YOU AGAIN! I love this forum and always recv such great advise/tips!

 

[cerfani]

Thank you for your help - however, Column B has 4 different salesreps (their names are PAUL, ANDY, NALINI and SUKHY). And I want to create a formula that recognizes these names and return a value in Column A (their initials - like what I detailed in Column A).

I could do a search & replace....but I just thought I could create a formula to do this as well.

THANK YOU AGAIN! I love this forum and always recv such great advise/tips!

You use combination of index and match then...

=INDEX(A1:A4,MATCH("PAUL",B1:B4,0))

this looks for the position of "PAUL" in B1:B4 and returns the value of the same position in A1:A4