Forum Discussion

smm10094's avatar
smm10094
Copper Contributor
Nov 12, 2020
Solved

Identifying sales reps who own current accounts

Hello! See attached for a worksheet.   I am importing new leads to my salesforce CRM. Before they are imported, I assign a sales rep to the account. However, some accounts are already assigned to ...
Formulas and Functions
  • PReagan's avatar
    PReagan
    Nov 12, 2020

    Hello smm10094,

     

    Please review the attached workbook which makes use of INDEX()+MATCH() and IFNA() functions. You may filter the Salesman column in the New Leads table to only show "No Salesman Assigned" to assign salesman to new leads.

     

    One other note: Some Account Names and Salesman names had extra spaces " " at the end. Make sure to omit these extra spaces because the MATCH() function searches for exact matches.

mathetes's avatar
mathetes
Gold Contributor
Nov 12, 2020

smm10094 

 

The formula is an easy one,

=XLOOKUP(B16,Table1[[Account Name ]],Table1[Salesman],"Unassigned",0)

 

However, you have some data integrity problems. Some (many, maybe all) of the Account Names in the second table had trailing spaces after the names, which meant an exact match wasn't made by Excel, which is very literal. I'm assuming you typed those in and that's why, but if your original data also has similar minor but very significant variations in how the names appear, Excel won't find the match.

 

I used the TRIM function to get rid of those (invisible to humans  but very visible to computers) trailing spaces.

Resources