The sheets of the following Excel file are divided into three types, of which Sheet A is the basic table, part of the data is as follows:

Sheet B1\B2…Bn are the association table, and all of these sheets have the same format, and have the same columns (Interval1, Interval2 and Interval3) as Sheet A. The figure below shows the data of Sheet B1:

Sheet C is used to describe the Join type between A and B1\B2..Bn. There are totally 3 types of joins, of which the cross Join represents Cartesian product, and both the leftJoinBig and leftJoinSmall represent the left association, and the associated columns are Interval1 and Interval2 respectively. See the following figure for details:

Calculation objective: associate sheet A with Sheet B1\B2..Bn according to the join type in sheet C, take the column Interva1 from sheet A, and take other columns from sheet B, and finally form a new two-dimensional table.
Let’s take the above sheet B as an example (actually each B should be different), if JoinType==crossJoin, the association result should be:

If joinType==leftJoinBig, the association result should be:

If joinType==leftJoinSmall, the association result should be:

Since this calculation needs to loop through sheet C and outputs multiple two-dimensional tables, it can only be implemented by using script instead of formula.
Script:

The script function case can judge the Join type, xjoin calculates the Cartesian product, and @1 represents the left association.

esProc Desktop and Excel Processing
12.11 Find changes through comparison
13.1 Row-to-column conversion for fixed columns