Standardizing output in google sheet with reference sheet

Question

This is my computatiom sheet: (https://docs.google.com/spreadsheets/d/e/2PACX-1vSR3jhKH7FNfuqo_VcemDUoZGNtoUJttCQz8XF0r7c8Qyyc4PWQqqHyxanalb5etA/pubhtml?gid=6282745&single=true)

Name              Country       Region  Unique Region   Region Count    Sales Representative
Mr Action Taker   Dubai         EMEA    EMEA                  5          Eashwari
Mr Responsible    Bangalore     APAC    APAC                  7          Chetan
Mr Careful guy    Mumbai        APAC    AMER                  3          Khushi

The unique region is computed by UNIQUE function which is giving me the list of unique regions from the inputs that i receive one by one. If a new region is added, the unique list will be appended automatically.

The region count is the count of regions in my input. I have use the MOD function to assign the lead to a sales rep in a chronological fashion so that even distribution is achieved.

In the above sheet, my region count increases by 1 as soon as a new entry is added into the input sheet. When that happens, a sales rep is assigned automatically.

Each region has 4 sales rep.

Now, i have an output sheet below. (https://docs.google.com/spreadsheets/d/e/2PACX-1vSR3jhKH7FNfuqo_VcemDUoZGNtoUJttCQz8XF0r7c8Qyyc4PWQqqHyxanalb5etA/pubhtml?gid=1954161251&single=true)

Name of the Lead    Team    Sales Representative    Notes:
Mr Action Taker     EMEA                            We want to upgrade our subscription

Now, I want to show the sales rep in the output sheet which i have computed in the computation sheet. This shall not change if my computation sheet is updated whenever new entry comes in. The sales rep shall be from his region team.

Answer 1

use:

=INDEX(IFNA(VLOOKUP(A2:A&" "&B2:B, {
 IMPORTRANGE("url_or_id_of_spreadsheet", "Part 2 Computation Sheet!A2:A")&" "& 
 IMPORTRANGE("url_or_id_of_spreadsheet", "Part 2 Computation Sheet!C2:C"), 
 IMPORTRANGE("url_or_id_of_spreadsheet", "Part 2 Computation Sheet!F2:F")}, 2, 0)))