Cross Multiply Rows From a Table with Columns from another based on match

[Wixard]

Hi all,

Am trying to do something fairly straightforward, but trying to wrap my head around how....

I have 1 input table, and 2 reference tables. All tables are from different sheets in the same Excel workbook

Input Table

Company	Sales	Revenue
A	120	200
B	240	600
C	100	200

Reference Table 1

Company	Location	Type	Sales	Revenue
A	US	Fixed	20%	35%
A	US	Variable	30%	15%
A	Europe	Fixed	25%	40%
A	Asia	Variable	25%	10%
B	Europe	Mixed	45%	35%
B	Europe	Variable	25%	15%
B	Asia	Fixed	30%	50%

Reference Table 2

Company	Location	Type	Sales	Revenue
Default	US	Fixed	20%	35%
Default	Europe	Variable	80%	65%

What I need to do is search the Company from the Input table against Reference Table 1 and split the Sales and Revenue absolute values in the input table by the percentages in Reference Table 1.

If a company is found in the input table which is not in the reference table, split the sales and revenue values based on Reference Table 2 (the default).

The Output table should look like this:

Company	Location	Type	Sales	Revenue
A	US	Fixed	24	70
A	US	Variable	36	30
A	Europe	Fixed	30	80
A	Asia	Variable	30	20
B	Europe	Mixed	108	210
B	Europe	Variable	60	90
B	Asia	Fixed	72	300
C	US	Fixed	20	70
C	Europe	Variable	80	130

Any ideas about the cleanest most elegant way to do this?

[KimLamNg · Accepted solution]

One way to do this.

Spoiler

[Wixard]

Worked perfectly - thank you so much!