Problem description

There are 3 data areas (highlighted by three different colors) in the Excel file book1.xlsx, as shown below:

We want to calculate a column of data corresponding from 1 to 12 following the specific calculation rule: search the numbers from 1 to 12 in turn in the three data areas, locate value at the corresponding position in data area 1; and, if the value is 0, get value at the same position from data area 2; if the value is 1, then take F + n (n is the current number under search). The expected result is shown in G and H columns in the following figure:

The task involves member location in a sequence, getting a member at a specific position of a sequence, getting column values from a table sequence, and the use of clipboard.

Directions

1.     Write a script: 

A1   Import data from the clipboard.

A2   Get values of column 1 and column 2 from A1 to concatenate them into a sequence for data area 1. 

A5   Loop numbers from 1 to 12 to get the position (sequence number) k of the current loop number from A4, and return the current result value + Tab(/t) + the number (which is at position k in A3 if the number at position k in A2 is 0, or F + the current number if the number at position k in A2 is 1).

A6   Concatenate members of sequence A5 with \n as a string and put it onto the clipboard.

2. Select data area A1:F6 in the Excel file and press Ctrl+C to paste it to the clipboard.

Then go back to esProc to run the program and then move to the Excel file, click G1 and press Ctrl+V to paste the result in.

Q & A Collection 

https://stackoverflow.com/questions/63932628/ho-do-i-list-values-of-an-array-in-a-column-based-on-values-from-other-arrays

[Attachment]: lookup.zip