Task description

Below is Excel file book1.xlsx:

We need to get values for Shift column. The rule is like this: check whether the time value under column B falls in between the time period from the value under column F and that under column G; if it does, return the value under column E in the current row to enter it under Shift column.

Directions

1. Start esProc

Download esProc installation package and free DSK edition license HERE. You will be prompted to load the license file when you run esProc for the first time.

2. Open the above Excel file to load the add-ins file ExcelRaq.xll (Click the Go button on Option>add-ins dialog). The file is located in [esProc installation directory\bin] directory. Refer related Excel documentation to learn how to do the loading if you are not familiar with it.

3. Enter the formula below in C2 and copy it both C3 and C4:

=INDIRECT("E"&esproc("=?.pseg(?)",F$1:F$3,B2))

esproc()function call ins esProc add-in to calculate the formula. The first quotation mark represents the first parameter, and the second one represents the second parameter. pseg() function locates the sequence number of segment that a certain value belongs to. ?.pseg(?) gets the sequence number of the segment where B2 falls in among the segments made up of values of F1:F3.

Here’s the desired result:

【Reference】 book1.xlsx