Need Help: IF AND and Search text from multiple columns

[punksterz626]

Hello Colleagues,

Hoping everyone is doing well. I'm having a hard time creating a formula that meets this scenario, hoping someone can help me.

I have multiple columns in the sheet, but to simplify I'll just list the columns that pertain to this scenario. I want to do a search criteria in the Description column, but only if Type column is "ShoeSales", if not just leave blank. Is this possible?

Type	Description	Search Criteria	Result Wanted FOR ShoeSales
ShoeSales	SS project ABCD	if column A =ShoeSales & Description has "ABCD"	SS ABCD
PantSales	PS project ABCD
ShoeSales	SS project ABCD	if column A =ShoeSales & Description has "ABCD"	SS ABCD
ShoeSales	SS project M&S	if column A =ShoeSales & Description has "M&S"	SS M&S
ShoeSales	SS project TTD	if column A =ShoeSales & Description has "TTD"	SS TTD
PantSales	PS project

 

[Eric W]

Welcome to the MrExcel forum!

How do you determine what codes (ABCD, M&S, TTD, etc.) that you want to look for? Do you have a table of them? What do you want if it says ShoeSales, but none of the codes you have listed is there? Or do you want anything after the word "project"? Or some other method?

 

[punksterz626]

Welcome to the MrExcel forum!

How do you determine what codes (ABCD, M&S, TTD, etc.) that you want to look for? Do you have a table of them? What do you want if it says ShoeSales, but none of the codes you have listed is there? Or do you want anything after the word "project"? Or some other method?

The codes are part of the description and they're listed after the project name at the end of the description, if that helps. They are mostly unique but not always, hence the reason I want an if statement to the Type. That will for sure pull the correct data.

What do you want if it says ShoeSales, but none of the codes you have listed is there? It is unlikely this will happen, but if it does, leave blank.

 

[Eric W]

I'm still hazy on how to recognize the codes. So far the best idea I've got is to just use the last word from the description. Try:

Book3
	A	B	C
1	Type	Description	Result
2	ShoeSales	SS project ABCD	SS ABCD
3	PantSales	PS project ABCD
4	ShoeSales	SS project ABCD	SS ABCD
5	ShoeSales	SS project M&S	SS M&S
6	ShoeSales	SS project TTD	SS TTD
7	PantSales	PS project
Sheet29

Cell Formulas
Range	Formula
C2:C7	C2	=IF(A2="ShoeSales","SS "&TRIM(RIGHT(SUBSTITUTE(B2," ",REPT(" ",100)),100)),"")

 

[Peter_SSs]

If you happen to have the TEXTAFTER function in your 365 version then you could try the C2 formula below.
If you do not have the TEXTAFTER function then you could try the modified version of Eric's formula in D2, which does not need to be copied down the column - it automatically 'spills' the other results down the column.

22 07 01.xlsm
	A	B	C	D
1	Type	Description	Result 1	Result 2
2	ShoeSales	SS project ABCD	SS ABCD	SS ABCD
3	PantSales	PS project ABCD
4	ShoeSales	SS project ABCD	SS ABCD	SS ABCD
5	ShoeSales	SS project M&S	SS M&S	SS M&S
6	ShoeSales	SS project TTD	SS TTD	SS TTD
7	PantSales	PS project
Search Text

Cell Formulas
Range	Formula
C2:C7	C2	=IF(A2:A7="ShoeSales","SS "&TEXTAFTER(B2:B7," ",-1),"")
D2:D7	D2	=IF(A2:A7="ShoeSales","SS "&TRIM(RIGHT(SUBSTITUTE(B2:B7," ",REPT(" ",100)),100)),"")
Dynamic array formulas.