Function returning value based on the conditions of the advanced filter

[kubaalbrecht]

Hello,

I am solving quite easy task but I am not able to find a simple solution.

My task:

• I am trying to categorize my bank account transaction from the XLS statement to several categories: meal, transport, entertainement atd. For every category I need to defined several conditions which need to be fulfilled to include the trasction (an excel row) to the category. For example "Entertainment ~ Description = *Spotify* OR Description = "*Netflix*" OR Description = "*YouTube*". Similar contitions then for other categories.
• As there will be like 20 contiditions in total and conditions may evolve during the time, I do not want to hard-code them to the formulas like "=IF(OR(C2="Netflix";C2="Spotify";C2="YouTube");"Entertainment";IF(OR(...contitions of another category...);"Another category";...etc...))) but used same kind of stand-alone criteria definitions similar to the definition of criterias for the Advanced filter.

Current solution:

• I have defined several areas defining criterias for Advanced filter which filters transactions from the defined Category like
  • A1: Filter_Entertainment
  • A2: Description
  • A3: ="=*Netflix*"
  • A4: ="=*Spotify*"
  • A5: ="=*YouTube*"
• Then I apply in sequence every single advanced fillter and manually assign the filtered transactions with the category tag, then continue to another filter

What I would apprepriate:

• the native function (rather then VBA) which returns the value based on the information if a specific row meets the criteria of a defined advanced filter
• or any other solution which automates my manual input of the category tag based on the advanced filter results

Thank you for your help.

Jakub Albrecht

[#2]

If you're able to upload a sample workbook, it would help tremendously. The IF-OR with 20+ logical tests is not necessary. You could create a lookup table and use VLOOKUP or XLOOKUP off the table to simplify the formula and it would be much easier to maintain. There may be better approaches than using advanced filter, but it's difficult to say without seeing the data arrangement.