Forum Discussion
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 a sale rep in my CRM.
What formula can I use to identify and automatically input my sales reps names into the "New Leads" which they already own? If the account is not assigned, I would like the text "Unassigned" to appear.
Thank you!
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.
5 Replies
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.
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.
