Nested Search

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

Answer:

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

Select cell B2
Type the formula above in formula bar
Press and hold CTRL + SHIFT key
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.




Advanced formulas Concatenate Based on a condition Unique distinct values Drop-down list Join without TEXTJOIN Lookup & return values Compare Criteria, two lists Sums, return unique Extract missing values Common values 3 lists Two price lists Records in two tables Common records Update recent values(2) Missing values two cols Update recent values Values shared by 2 rngs Vals not shared 2 rngs Values shared by 3 rngs Extract not shared vals Shared values 2 cols Missing values 2 cols Combine merge Merge tables Merge rows - condition Combine ranges [UDF] Merge matching rows Merge 2 cols w. blanks Consolidate sheets Combine sheets Merge two columns Merge 3 columns Count values Pattern in cell value Overlapping time Containing any list value Unique distinct/condition Count colored cells Equal values per row Count overlapping days Sort count freq. condi. Count overlap. days(2) Count overlap. days(1) Contiguous values Count students Count filtered table Groups of each value Weekday in date range Weekday date range(2) Cells between value Entries date & time rng Unique distinct - criteria Unique dist. rec. criteria Digits - no duplicates Based on criteria Count groups in calendar Uniq. dist. based dates Count based on date Numbers across sheets Cells between values Most freq/date range Unique distinct months Uniq. dist. 2 cols Unique dist. values Dates Schedule -> date rngs Overlapping time Count overlapping days Date from week & day Count overlap. days(2) Count overlap. days(1) Missing dates Overlap. date ranges Overlapping ranges Find overlap. ranges Min/max date overlap. Date ranges overlap Consecutive dates Count overlap days Prevent overlapping Find date range based on a date Create date ranges Weekday in date range Calc dates in project Weeks from ranges Weekday date range(2) Entries date & time rng Overlap. date ranges Overlap rngs - condition Count groups in calendar Dates from overlap rngs Dates from date rngs Date rngs from dates Dates not in rngs Overlapping ranges Based on date criteria Date, week, month Dupes - date, wk, month Year - month from dates Match date in date rng Latest date - condition Most freq/date range Find min/max date rng Blank between quarters Dupe vals sorted date Create date range Lookup the nearest date Based on a date range Machine utilization Date based on wkday Find numbers in sum Numbers closest to sum No net close to zero Find numbers in sum Numbers in sum Overlapping Overlapping time Count overlapping days Count overlap. days(2) Count overlap. days(1) Missing dates Overlap. date ranges Overlapping ranges Find overlap. ranges Min/max date overlap. Date ranges overlap Count overlap days Prevent overlapping Overlap. date ranges Overlap rngs - condition Overlapping ranges Unique values Case sensitive Unique strings [UDF] Sort digits in rng Based on date criteria From range Sort unique A-Z Min unique number Sort based on date Sum if unique Two columns One column Unique distinct values One column Two columns Three columns Remove blanks Uniq. dist. - condition Adjacent contains string Adjacent has text Contains string Based on criteria Sorted frequency Sorted by sum Sorted alphabetically Sorted A->Z Sorted & no blanks - rng Sort length From a cell range Sorted A-Z from range Contains string in rng Begins with criterion Based on a date range Based on dates Year - month from dates Based on date criteria Extract filtered table Extract & sum condition Two conditions Sum uniq. dist. rec. Assign randomly unique Sums, return unique Sorted, no blanks - rng Duplicate values Dupes A-Z condition Dupes w. exceptions Filter duplicate values using critera Common values 3 lists Label duplicate records Dupes - date, wk, month Dupes from rng contain Filter dupes begins w. Min unique number Dupe vals sorted date Sort dupes A-Z Dupes from range Dupes from 3 cols Dupes from 1 col Dupes from 2 cols Create a list of duplicates where adjacent cell value meets a condition Sort values Sort a column A-Z Sort from 2 cols A-Z Sort rng A-Z Sort column - frequency Frequency - criteria Sort rng - frequency Sort count freq. condi. Sort two columns Sort records Sort table Sort adj. txt horiz. (1) Sort monthly Sort table custom Lookup/Sort custom Sort random order Sort based on length Reverse list Sort dupes A-Z Sort unique A-Z Sort unique A-Z blanks Sort cell delimiter [VBA] Sort Excel table [VBA] Sort based on proximity Sort dates - criteria Vlookup function VLOOKUP function Discount percentages Multiple conditions Lookup values in rng Lookup value in rng Three columns Two datasets Vlookup and return multiple values 5 easy ways Multiple conditions Sorted from A to Z Sorted A-Z - condition Multiple conditions Vlookup range Vlookup rng - records Vlook col - return rng 1 result per search Horizontal vlookup Vlookup - Drop-down Multiple sheets Search and return multiple values Nested Search Multiple conditions Interactive formula Search for string Search records Wildcard vlookup Multiple strings Criteria - AND logic Strings in any 2 cols Strings in 2 cols each Strings in column Contains string - column Filter records Extract using criteria Extract using criteria(2) Extract using criteria(3) Recs betw. this & that Records betw. 2 dates Date range & condition Search text string Filter neg. values Containing digits Records Sort based on freq. Not shared records Sort table custom Records [AND logic] Records in two tables Unique records Common records Common records Duplicate columns Unique distinct records Extract unique distinct records from two data sets Sort based on freq. Filter unique distinct records using criteria Count uniq. dist. [2010] Unique dist. rec. criteria Records - condition(2) Records - condition(1) Filter unique distinct records Count unique distinct records Count unique distinct records Duplicate records Extract duplicate records Count records Weekly Blog EMAIL Get a FREE PDF book! Email Formula categories Count Dates Extract Index and Match Logic Lookups String manipulation Sum Time Vlookup Welcome! I am Oscar and here to help you out. Feel free to comment and ask Excel questions. Make sure you subscribe to my newsletter so you don't miss new blog articles.	Nested Search Excel > Search and return multiple values > Nested Search Share on Facebook. Share on Twitter. Share on LinkedIn Pin It! 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 Answer: 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 Select cell B2 Type the formula above in formula bar Press and hold CTRL + SHIFT key 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.

get-digital-help.com

Nested Search

Answer:

How to enter an array formula

How does the array formula in cell B2 work?