Combining Nested IF(OR)(AND)FORMULAS

[Maru]

Hi,

I have 3 individual nested IF formulas that I am attempting to combine into 1 with no luck. All IF formulas work correctly on their own but I'm unable to join them together without getting an error.

I will upload a sample spreadsheet but the formulas are as follows:

=IF(OR(ISNUMBER(SEARCH("Bright*",C4)),ISNUMBER(SEARCH("Com*", C4)),ISNUMBER(SEARCH("Via*",C4))),"C","NO")

=IF(OR(ISNUMBER(SEARCH("out*",C3)),ISNUMBER(SEARCH("CLEAR*", C3)),ISNUMBER(SEARCH("LAMAR*",C3))),"O","NO")

=IF(OR(ISNUMBER(SEARCH("TAMPA BAY*",C2)),ISNUMBER(SEARCH("TAMPA TRIBUNE*",C2)),ISNUMBER(SEARCH("TAMPA BAY NEWS*",C2)),ISNUMBER(SEARCH("LEDGER*",C2)),ISNUMBER(SEARCH("NEIGHBORHOOD*",C2)),ISNUMBER(SEARCH("OBSERVER*",C2))),"PN","NO")

Any help to combine the 3 formulas into one formula would be very much appreciated.

Thanks,
Mary

 

[Shteven]

I've noticed all of your TRUE values are different, so I don't think we can combine them yet without knowing more. What are your expected results by combining all 3?

 

[Maru]

I've noticed all of your TRUE values are different, so I don't think we can combine them yet without knowing more. What are your expected results by combining all 3?

My typical monthly spreadsheet for this has 500-700 lines of data in it for vendors that we use and one of the functions I am trying to achieva is to write a formula that would pick the most common alpha statements, such as "Lamar", "Clear", "Out", etc, for the vendors, and then apply a specific Media Type Code to that line item.

I've written individual formulas for 3 media types so far; Outdoor - "O", Print Newspaper-"PN", and Cable -"C". I'd like to have just one formula that covers all the Media Types, or at least the majority of them, and use just the one formula instead of manually searching for the vendor and then pasting in the correct formula.

Does that help or make sense?

Thanks!

 

[Nebu]

Hi:

Use the following formula

=LOOKUP(2^15,SEARCH($P$2:$P$10,C2),$Q$2:$Q$10)

Thanks

 

[Shteven]

My typical monthly spreadsheet for this has 500-700 lines of data in it for vendors that we use and one of the functions I am trying to achieva is to write a formula that would pick the most common alpha statements, such as "Lamar", "Clear", "Out", etc, for the vendors, and then apply a specific Media Type Code to that line item.

I've written individual formulas for 3 media types so far; Outdoor - "O", Print Newspaper-"PN", and Cable -"C". I'd like to have just one formula that covers all the Media Types, or at least the majority of them, and use just the one formula instead of manually searching for the vendor and then pasting in the correct formula.

Does that help or make sense?

Thanks!

Nebu's setup is the way to go. You would create a table of Keywords and Media Type Codes, then do a LOOKUP formula against that table to pull in the appropriate Media Type Code for each Vendor. This would give you much more flexibility for adding & removing keywords and codes in the future.

 

[Maru]

Thank you Nebu and Shteven. That worked perfectly!