Find Positions of Values and Then Get Corresponding Values by Positions
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:
A 

1 
=clipboard().import() 
2 
=A1.(_1)A1.(_2) 
3 
=A1.(_3)A1.(_4) 
4 
=A1.(_5)A1.(_6) 
5 
=12.((k=A4.pos(~),~/"\t"/if(A2(k)==0,A3(k),"F"/~))) 
6 
=clipboard(A5.concat("\n")) 
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.
[Attachment]: lookup.zip