Using Multiple IF Criteria based on search on Text

[questforexcel]

Hello People,

I have 3 queries and would appreciate your assistance on them:

1) I have a specific concern which I've written in more detail after these 3 listed queries, on how to write a formula which will help group bulk of text data into the desired categories.

2) Could you recommend any free e-book or tutorial to develop advanced skills in excel.
I am not that confident with the IF, nested if and Macros. I would like to gain a solid conceptual knowledge around it.
Could you guys please help and guide me to some effective self study courses?

3) The best mode to learn the Power BI on excel.


On my Query no.1 :

Here is what I need to do:

From a range of 250 cells where expenses have been classified in repetitive or different ordered names, I need my formula to categorize the expenses into a set of specific text categories:

For eg:

[TABLE="width: 125"]
[TR]
[TD]FB Advertising[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 125"]
[TR="bgcolor: transparent"]
[TD]Office Supplies[/TD]
[/TR]
[TR]
[TD][TABLE="width: 125"]
[TR="bgcolor: transparent"]
[TD]Web[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD]Internet
Bank

[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD]The above 5 expenses can be classified into "Advertising & Promotion"

The formula worked on the 1st cell but when I pulled it down it showed "#VALUE!"

Below is the formula I used.

= IF(FIND("Advert",D3,1),"Advertising & Promotion", IF(FIND("bank",D3,2),"Accounting & Finance Fees")*IF(FIND("print",D3,1),"Purchases - Printing"))
[/TD]
[/TR]
[/TABLE]

[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD][/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD][/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD]Could you please help me with a formula which can help me put the multiple condition text criteria. If its A, then "ABC", if X, then "XYZ", if Z, then "ZZZ".

Thank you

[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD][/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD][/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD][/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD][/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD][/TD]
[/TR]
[/TABLE]

[/TD]
[/TR]
[TR]
[TD]
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[/TABLE]

 

[SpillerBD]

FIND() is case sensitive.
SEARCH() is not case sensitive

 

[SpillerBD]

1) I have a specific concern which I've written in more detail after these 3 listed queries, on how to write a formula which will help group bulk of text data into the desired categories.

2) Could you recommend any free e-book or tutorial to develop advanced skills in excel.
I am not that confident with the IF, nested if and Macros. I would like to gain a solid conceptual knowledge around it.
Could you guys please help and guide me to some effective self study courses?

3) The best mode to learn the Power BI on excel.

Simple answer to all this, and free. ExcelIsFun. Mike Girvin's YouTube Channel.
For PowerBI also look into the P3-nation at https://powerpivotpro.com/
Also look for Avi Singh on YouTube, who cowrote with Rob Collie the original book on "PowerPivot and PowerBI"

Learning the Query, M-Language is pretty easy in the basics. In depth sources will cost. Check the HolyMacro publishing store, the Mr Excel Store, for the Monkey book. Gil Raviv has a new book out too, but I would still start you on Puls/Escobar's book.

 

[questforexcel]

Thank you so much for the speedy response much appreciated.

Simple answer to all this, and free. ExcelIsFun. Mike Girvin's YouTube Channel.
For PowerBI also look into the P3-nation at https://powerpivotpro.com/
Also look for Avi Singh on YouTube, who cowrote with Rob Collie the original book on "PowerPivot and PowerBI"

Learning the Query, M-Language is pretty easy in the basics. In depth sources will cost. Check the HolyMacro publishing store, the Mr Excel Store, for the Monkey book. Gil Raviv has a new book out too, but I would still start you on Puls/Escobar's book.

 

[questforexcel]

Is there a way to use filter functions on text search such that, I could type in all the conditions under which one filter would be applied:
for eg:
IF a cell D2 contains "A" or "B" or "C", then type "Criteria 1"

IF a cell D2 contains "X" or "Y" or "Z", then type "Criteria 2"

What I have used currently is IFS(ISNUMBER(SEARCH("Advert",D2)),"Advertising & Promotion"
If I have to type out individual formulas for every unique word, my formula has become very long.

Could you help shorten and provide an effective formula on this.
​

 

[mail2master]

You are using IF() function but didn't give any logical test.... FIND() doesn't perform a logical test.
According to my understanding, what you are trying to do is find out if "Advert" matched with the text in D3. If "Yes" then Output would be "Advertising & Promotion"

=IF(ISNUMBER(FIND("Advert",D3,1)),"Advertising & Promotion", IF(ISNUMBER(FIND("bank",D3,2)),"Accounting & Finance Fees")*IF(ISNUMBER(FIND("print",D3,1)),"Purchases - Printing"))

This formula should work.... If it doesn't, try replacing FIND() with SEARCH().

 

[mail2master]

Sorry, I didn't realize that you already got the correct formula. Please ignore my previous post.
In order to shorten the formula, you need to make a table comprised of "Key Words" viz Advert, bank etc... and respective "Expense Head" viz. Advertising & Promotion.
Instead of writing the whole text inside the formula, try to link the cells of table you have created.