Excel 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 , 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:



Use the SPL XLL plug-in of Excel

Write the formular in a blank cell:


As shown:



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


[Attachment]: lookup.zip