Using Multiple IF Criteria based on search on Text

questforexcel

Board Regular
Joined
Jan 18, 2019
Messages
128
Office Version
  1. 2013
Platform
  1. Windows
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:

FB Advertising
Office Supplies
Web
Internet
Bank

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"))

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



 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
FIND() is case sensitive.
SEARCH() is not case sensitive
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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().
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top