If Statement with Multiple Named Range Criteria

Hi, I'm working on organizing a credit card statement.

I've made some or bunch of named ranges to categories the statement. I decided to make an additional column that stated weather this line had been added to a named range.

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(NamedRange1,C2))),"Done","Not Done"))

This works well. To add another Named Range to be check I made an additional IF statement.

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(NamedRange1,C2))),"Done",IF(SUMPRODUCT(--ISNUMBER(SEARCH(NamedRange2,C2))),"Done","Not Done")))

As you can imagine this is getting ridiculous in size. But, I can't seem to figure out how to add multiple named ranges to be search/checked against C2.

For Example:
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(NamedRange1,NamedRange2,C2))),"Done","Not Done"))
or
=IF(SUMPRODUCT(--ISNUMBER(SEARCH({NamedRange1,NamedRange2},C2))),"Done","Not Done"))
Obviously neither of these worked....

There is probably a much way better solution for this, which I'm would love to hear. Or Any additional thoughts.

Thank you!

Hi Dflak,

Made a quick mock of the situation.

As I mentioned It currently works but the If statement is going to end up ridiculously big. What i'm trying to do is make Expense!C:C an easier statement to encompass multiple Named Ranges without having to make another if statement portion.

Hope that makes sense.

Hi dflak,

Isn't the pivot table still based off Expense!C:C. That's what I'm trying to consolidate.

Which is this:

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(Restaurants,A2))),"Done",IF(SUMPRODUCT(--ISNUMBER(SEARCH(Utility,A2))),"Done","Not Done"))

Trying to make it so each Named Range does not need an additional If statement.

Some way to include NamedRange1 and NamedRanged2 into the the first If statement.

I think I'm following what you are saying but I'm not 100% sure....

What I'm running into is Transportation1, Transporation2, Transporation3, will be under it's own Criteria List with it's own DefinedRange which then would have to be added to the Added to "Added to Criteria" formula.

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(Restaurants,A2))),"Done",IF(SUMPRODUCT(--ISNUMBER(SEARCH(Utility,A2))),"Done",IF(SUMPRODUCT(--ISNUMBER(SEARCH(Transportation,A2))),"Not Done")))


So, what I have is a couple of statements with 1500 items, I was putting together some DefiniedRange to use as a Criteria List. I wanted to add a column to reflect if I've added that criteria to the multiple DefinedRange lists. But, my formula in Added to Criteria is growing by
IF(SUMPRODUCT(--ISNUMBER(SEARCH(Restaurants,A2))),"Done"
on every DefinedRange I make. I was hoping for something simplified.

Maybe I'm missing what you are saying.... I'm not sure.

Try:

=IF(COUNT(SEARCH(Restaurants,A2),SEARCH(Utility,A2)),"Done","Not Done")

Enter with Ctrl+Shift+Enter

Hi Phuocam,

Awesome, that worked perfectly! Exactly what I was looking for!

Thank you!