Help with an excel sheet that needs a lot of IF statements

[dburg27]

I'm going to do my best to explain this:

We have an excel document with multiple columns, two of them being category and the other department. I also have a blank column. I need to find a way to have a formula or VB script or macro look at the category and department column and if the category column has for example Restaurants written in it as well as in the department column it says Marketing then i need the formula/VB script/macro to enter 6215-00-00 into the blank column. I have several different category and department combos all with different numbers and I'd like to be able to add to it when a new one comes along. I have found a way to make it work with an IF(AND()) statement but excel only allows 64 of them which i fear won't be enough. So not sure if there is a better way to go about doing this.

Thank you in advance for any assistance!

[#2]

@dburg27 

I'd create a list of categories, departments and codes, and convert it to a table. In the screenshot below, the table is named Codes.

You can then use this table in a formula. If your data are in a table too, you only have to enter it in a single row, and Excel will automatically propagate it to the rest.

The formula in the screenshot below is

=IFERROR(INDEX(Codes[Code], MATCH(1, (Codes[Category]=[@Category])*(Codes[Department]=[@Department]), 0)), "")

If you don't have Microsoft 365 or Office 2021, confirm the formula by pressing Ctrl+Shift+Enter.