mardi 16 février 2021

Excel IF Statement Limited

I am using an IF statement in Excel to search for portions of text in the previous column in order to assign a supplier and category to the expense.

Supplier Column

=IF(ISNUMBER(SEARCH("tit",[@Description])),"TITAN",IF(ISNUMBER(SEARCH("Sol",[@Description])),"Soltrack",IF(ISNUMBER(SEARCH("coin",[@Description])),"Coin",IF(ISNUMBER(SEARCH("gree",[@Description])),"Green Dream Projects",IF(ISNUMBER(SEARCH("sars V",[@Description])),"SARS VAT",IF(ISNUMBER(SEARCH("sars p",[@Description])),"SARS PAYE",IF(ISNUMBER(SEARCH("acb",[@Description])),"Debit Order","")))))))

Category Column

the next column then has the following to get the category of the supplier

=IF(ISNUMBER(SEARCH("TITAN",[@Payee])),"Direct Operating Cost",IF(ISNUMBER(SEARCH("Soltrack",[@Payee])),"Direct Operating Cost",IF(ISNUMBER(SEARCH("Coin",[@Payee])),"Direct Operating Cost",IF(ISNUMBER(SEARCH("Green Dream Projects",[@Payee])),"Direct Operating Cost",IF(ISNUMBER(SEARCH("SARS VAT",[@Payee])),"VAT",IF(ISNUMBER(SEARCH("SARS PAYE",[@Payee])),"PAYE",IF(ISNUMBER(SEARCH("Debit Order",[@Payee])),"Debit Order","")))))))

this is working great, but seems i have reached the limit (7) of IF statements I can use in one formula?

Could I edit this in some way to allow more to be added, or do I need a new formula?

Aucun commentaire:

Enregistrer un commentaire