Automate lead allocation in excel

Welcome to the forum.

You may be able to use PowerQuery for this.

However, there are issues with your sample workbook:

1. There is no apparent link between the Input sheet and the Info sheet - how would we know which part of the hierarchy was being referenced?
2. You have provided no sample results on the Output sheet, so we have no idea what you are looking to achieve.

Pleae replace the workbook with one that addresses these issues.

Hi,

Please find the updated sheet with an example of the output to be generated

OK - we are getting there slowly.

Excel will not know that Dubai matches the EMEA region. You will need to have a lookup table somewhere in your workbook that shows which locations are in which region, otherwise you can't match anything up.

Final question: how did you determine that this staff member should be representative E and not F, G or H?

It's not at all clear to me what the output list is meant to achieve. In fact, it seems completely pointless as it stands.

OK - I don't get it. Sorry - I'm out.

One single sample result does not illustrate what you want. I can't be bothered trying to drag the detail out of you - I don't have the time or the patience to do so today. Hopefully someone else will be able to read your mind.

I am going to make a guess here and suggest that you try the following:
1. Populate column A using: =Input!A2
2. Populate column B using: =Input!C2
3. Apply conditional formatting with the rule: =A2=0 (font = fill which is yellow in this case)
4. Populate column C using: =IF(A2=0,"",IFERROR(INDEX(INDIRECT(B2),MOD(COUNTIFS(B$2:B2,B2),COUNTA(INDIRECT(B2)))),INDEX(INDIRECT(B2),COUNTA(INDIRECT(B2)))))
Note that Name Ranges have been produced from the lists in K1:M5 on the Info sheet.
Let us know if you have any questions.

@jj
Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread