why do you need the -1000 and 1 at either end of the Cum Duration row?

That relates to the LOOKUP function. Let's just extract one part of the fomula:

LOOKUP(

D$5-$C6,

$D$3:$L$3,

$D$1:$L$1)

LOOKUP searches for the

red value, in the

blue range and returns the corresponding value from the

green range. For LOOKUP to work, the blue range must be sorted in ascending order. If lookup cannot find the red value it drops down to the next value lower than the red value.

Look at this part of the formula in D6. The red part is

D5 - C6 = 12 Nov - 7 Nov = 5

So LOOKUP looks for 5 in the blue range. 5 does not appear in the blue range so LOOKUP drops to the next value lower than 5. This is 0 and so LOOKUP returns the corresponding value from the green range, which is "Site"

Look at this part of the formula that has been copied down to D8

LOOKUP(D$5-$C8,$D$3:$L$3,$D$1:$L$1)

D5-C8 = 12 Nov - 20 Nov = -8

So LOOKUP looks for -8 in the blue range. -8 does not appear so it drops down to the next value lower than -8. This is -1000 so LOOKUP returns the corresponding value from the green range, which is "". So, if -1000 (or some similar value) wasn't there, the formula would error because it cannot find a number lower than -8.

I didn't do the other end of the range quite as efficiently but since it is working let's leave it as is. It works basically the same. If the 140 wasn't there, then when VLOOKUP was looking for a number >139 (this will occur when the row 5 date is > 139 days ahead of the start date) it wouldn't find that number so it would drop back to the next value lower which is 139 and would return "PCI". That would mean you would get "PCI" in all cells > 139 days after the Start Date.

Hope that made some sense. As is usual, it is easier to write a formula than explain it.