I'm trying to Assign employee code (EmpID) to my customer database in Excel
I've the list of customers from each store which is ranked and it should be allocated to the employees of the respective store.
I've customer database in the following format Column name is given in brackets
(A). (B). (C)
Store - CustomerID - Rank
A. - A01. - 1
A. - A02. - 2
B. - B01. - 1
B. - B02. - 2 and so on
I've employee database in the following format
(D). (E). (F). (G)
Store. - EmpID. - Rankfrom - Rankto
A. - E1. - 1 - 10
A. -E2. -. 11. - 16
B. - E3. -. 1. - 8 and so on
I'm trying to add one more column to employee table where EmpId is assigned based on store and rank of customer in relation with rank range assigned in table 2
I've come up with formula, but not working as I expected . Please help since I'm new with array functions
{=Index(D:G,Match(1,((F:F>=C2)*(G:G>=C2)*(D:D=A2),0)2)}
Desired output will be to add an employee code against each customer ( matching store and range of rank allotted to each employee )
Please see the uploaded excel, the logic is working but not the way i had in mind.
Thanks in advance
Bookmarks