Complex value calculation issue

[FemaleCEO]

Hi forum!

I'm currently looking at data pulled from my sales team, and I'm having issues getting accurate reporting on sales per sales rep. The issue is that we have a split model between straight up software license sales, and SaaS. 

I have one report which shows sales reps, and an ID number for the customer order - for the direct sales. However, the way our BI guys have set up the system, when I extract the SaaS sales, I only see the sales ID - no sales rep connection, in other words. 

This poses somewhat of a problem. We have almost 300 sales reps across Europe, making tens of thousands of sales every month. And we give out bonuses based on those sales. I want to make sure they're accurate.

Is there any way, dear Excel gods, to set up a formula which connects sales rep ID with sales ID, and then extracts the SaaS numbers connected to that Sales ID, and ties it to the sales rep ID?

Basically, let's say Sales Rep A has made Sale B1, which also has a monthly SaaS "bonus" attached to it. I can se "B1" connected to those numbers in the SaaS report, but I need the B1 SaaS sales also connected to Sales Rep A, because they do contribute to the Sales Rep A monthly TO. If that makes sense?

Appreciate any help!

[#2]

@FemaleCEO 

I would suggest you ask the BI team to taylor a report that can bring everything you need.

Just answearing the question, you can retrieve information using the combination of the formulas

INDEX+MATCH or XLOOKKUP or VLOOKUP or create a relationship with your data with powerquery.

Lets say you have a table with Ids and Names (SaleRepresentative) and you have another table only with IDs(SaleReport). What you need to do is identify the position of the IDs you are looking for in the SaleRepresentative table.

INDEX(ColumnWithNAMESOnSaleRepresentative,MATCH(SaleId,ColumnWithIDsOnSaleRepresentative,0),1)

INDEX(SalesRepresentative!B2:B100,Match(A2,SalesRepresentative!A2:A100,0),1)