I have got a ton of bid-related information from the Government E-procurement site in India. I have columns like Ministry, Organization, Department, and Office which are in a hierarchical fashion like multiple offices inside Department with multiple departments inside an organization and organizations inside a ministry. I have a separate list of accounts that have salespeople aligned to it. Now my problem is to find a formula that will help me align the opportunities to each salesperson. I want the formula to go like this, first, it filters out all the results based on Ministry, then out of those filtered results, it filters out the Organization/Department/Office and returns the value from the list of accounts that have salespeople aligned to it. I also want it to skip if Organization is not present and move on to Department.
I tried using VLOOKUP after text-joining Ministry, Organization, Department, and Office, but it matches only the first word and hence the matches are not accurate.
I had a formula passed down to me but couldn't make any sense of it.
=IFERROR(VLOOKUP([@[Organization Name]],ISR_alignment!$G$2:$H$22,2,FALSE),
IFERROR(INDEX(ISR_alignment!$A$2:$A$72922,MAX(IF(ISERROR(FIND(LOWER(
ISR_alignment!$A$2:$A$72922),LOWER([@MDOOA]))),-1,1)*(ROW(
ISR_alignment!$A$2:$A$72922))-ROW(ISR_alignment!$A$2)+1)),""))
