Using Match and Search together

[dwshearer]

Hello all,

I am new to the forum, but not new to Excel. I am in the category of users as "knows enough to be dangerous" but I still don't feel that I am an expert by any stretch of the imagination.

I have a spreadsheet that I created for my job. I work for a convention center and I created a report to count the number of event days that we have in a year. I pulled the data from our database, which only gives us the all the days a client is booked. Often times there are set-up days in the booked days, and I want to get to just the days of the actual event. There are several keywords that I have identified in the Event Name to let me know if there are set-up days or not for different event categories that I can search on to find those events that have set up days and those that don't. I wrote a formula that is working, but I can't help but think that there is an easier way to write this formula.

I think there is something with Match and Search, but I cannot figure this out. If anyone could help me streamline this formula, I would be greatly appreciative.

The formula I wrote is:

=IFERROR(IF(ISBLANK($D$3),"",
IF(AND(ISNUMBER($F57),ISNUMBER($E57),$F57=$E57),1,
IF(OR(AND($C57=Word_Search3,$H57<=2),AND($C57=Word_Search5,OR(ISNUMBER(SEARCH(NFP_Word1,$D57)),ISNUMBER(SEARCH(NFP_Word2,$D57)),
ISNUMBER(SEARCH(NFP_Word3,$D57)),ISNUMBER(SEARCH(NFP_Word4,$D57)),ISNUMBER(SEARCH(NFP_Word5,$D57))))),$H57,
IF(AND($C57=Word_Search4,OR(ISNUMBER(SEARCH(Corp_word1,$D57)),ISNUMBER(SEARCH(Corp_word2,$D57)),ISNUMBER(SEARCH(Corp_word3,$D57)))),$H57-1,
IF($C57=Word_Search4,$H57,IF(ISNUMBER(SEARCH(Word_Search1,$D57)),$H57,
IF(ISNUMBER(SEARCH(Word_Search2,$D57)),1,
IF(AND($C57=Word_Search3,$H57>=3),$H57-1,$H57-1)))))))),"")

If there is any other piece of information that is needed to help solve this, please let me know.

Not sure how to paste the excel sheet on here, so I have a link to my onedrive where the sheet is sitting for anyone that would like to help. The above formula is in column I starting in row 11.

Link to workbook is located at this address. 1drv.ms/x/s!AnoBlX9UxYb56BKlE6F9D8er1HvC?e=uFJGA4

Thank you again,
David

[The_Snook]

Hi dwshearer,

Welcome to the forum! It's always easier for people to provide solutions if you upload an example of the workbook/data you are working with (see yellow banner above for guidance on how to upload workbooks).

Regards,

Snook