Problem description

There are 3 data areas (highlighted by three different colors) in the Excel file , 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 data areas 3, 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:

Solution

Use the SPL XLL plug-in of Excel

Write the formular in a blank cell:

=spl("=12.(k=?3.conj().pos(~),[~,if(?1.conj()(k)==0,?2.conj()(k),""F""/~)])",A1:B6,C1:D6,E1:F6)

As shown:

Explanation:

Loop numbers from 1 to 12 to get the position (sequence number) k of the current loop number in data area 3, then return the current loop number and the number (which is at position k in data area 2 if the number at position k in data area 1 is 0, or F + the current number if the number at position k in data area 1 is 1).

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