Forum Discussion
Help with an excel sheet that needs a lot of IF statements
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 ...
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.