Author: Oscar Cronquist Article last updated on February 01, 2019

Minh Hung asks:

Hello Mr Oscar
I have the matter to create a megaformula to categorize my list. For short example:A1: Cash in deposit (Branch A t/t)
A2: Borrowed from Corp. A
A3: Interest payment
A4: Int.panalty pmt
A5: Prin. Pmt
A6: Salary Pmt on April
A7: Sales abroad
A8: Branch C t/t
A9: Transferred from Company AA
A10: Mortgages to DD ltd
A11: Sal. Pmt on Mayand
at B1 cell, I create a formula as follows:=IF(COUNT(SEARCH({"branch","corp.", "company"},A1))>0,"Precol.", IF(COUNT(SEARCH({"interest","int.", "prin."},A1))>0,"lo.",IF(COUNT(SEARCH("sales", A1))>0,"Sa.",IF(COUNT(SEARCH({"sal.","Salary", "wage","payroll"},A1))>0,"Se.","Others"))))But, my formula is too long and too many parentheses. I want to shorten this formula or replace by another. But how? Could you please to solve my question?Thank you very much.Hung


By examining your formula it seems that you have different levels with search words. The word found with the lowest level should be returned, leave the remaining.

Example 1, cell A2 contains "test branch wage". "branch" is found on level 1 (cell E3) and "wage" on level 4 (cell F6). However "branch" is on the lowest level of the two so Precol. (cell H3) is returned in cell B2.

Example 2, cell A3 contains "abc sales payroll". "sales" is a search string found on level 3 and "payroll" is on level 4. Level 3 is the lowest level so "Sa." (cell H5) is returned in cell B3.

Example 3, cell A4 contains"anything whatever" and no search value is found except the asterisk (*) on level 5. Text string "Others" (cell H7) is returned in cell B4.

Array formula in cell B2:

=INDEX($H$3:$H$7, MIN(IF(ISNUMBER(SEARCH($E$3:$G$7, A2)), MATCH(ROW($E$3:$G$7), ROW($E$3:$G$7)), "")))

How to enter an array formula

  1. Select cell B2
  2. Type the formula above in formula bar
  3. Press and hold CTRL + SHIFT key
  4. Press Enter

If you did above steps correctly excel automatically adds a beginning and ending curly bracket {array_formula} to the formula. Don't enter these characters yourself.

How does the array formula in cell B2 work?

You can easily follow along as I explain this array formula. Select cell B2 and go to "Formulas" on the ribbon, click "Evaluate Formula" button. Click on "Evaluate" to go to next step.