We have a scoring data table for children’s height. In this table, the cells A1, B1 and C1 are different age ranges, and below them are the height data (cm); column D lists the scoring data.

The following table stores the children’s information:

Now we want to find out the scoring data in Sheet2 according to Age and Height in Sheet1, and fill in the results in column D. To achieve this, enter in cell D2:

Then drag D2 down to every relevant row:

A1: Convert the data of Sheet2 to a table sequence, and use the rvs function to reverse the order to make the heigh data arranged in ascending order
A2: Take out the column names of A1, and remove the last column name; Split each column name with a minus sign, and then convert the first one to an integer to form a sequence, i.e., [6,8,10]. In this sequence, find out the segment number where the current Age is located.
A3: Use the Height value to search the sequence interval formed by the values of A2-th column of A1 to find its corresponding row, and take the score of this row and return

esProc Desktop and Excel Processing
12.6 Use association table to handle interval association
12.8 Use interval range to perform retroactive searching of association table