Excel: Function for matching a given date between two dates

Question

A sheet of mine lists a series of stages (currently stages 2-12, as stage 1 is already complete). Each stage has a number in col A, a start date in col B, and an end date in col C. Stages are contiguous, and listed in order from earliest to latest.

Is there a way I can return, for a given date, the stage it falls into? I have a function in vba but I was hoping there might be some formula built in (or some way to jury rig a bunch of built-in formulae). Basically I want returned the number of the stage which has a start date smaller than or equal to a given date, and an end date greater than or equal to that date. Thanks to anybody who can assist.

Answer 1

You can use an Index/Match formula. The Match returns the number of rows into the range for the first date in the column that's less than or equal to the date you're looking for. The Index then uses the number returned by the Match to find the correct stage.

A Vlookup does something similar in one step, but it can't look to the left, and Index/Match is cooler. Note that you don't need an end date. Here's the formula:

=INDEX($A$2:$A$7,MATCH($D$2,$B$2:$B$7,1))