Here's an explanation I wrote up a while back for someone. The formula works

exactly the same way except that the criteria in this case is different.

=IF(ROWS($1:1)<=COUNTIF(B$2:B$8,"vac"),INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A

$8)-ROW(A$2)+1),ROWS($1:1))),"")

The only part of the formula that you actually need is this:

INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))

However, if you drag copy down, once the data that meets the criteria is

exhausted the next cell(s) will return #NUM! errors. Errors are unsightly

and can cause problems in any downstream calculations. We can build an error

trap in the formula that catches these errors so that they're not displayed

and won't affect any downstream calculations.

Excel has some error testing functions like : Iserror, Isna, Error.Type.

Using the Iserror function to test for errors and "trap" them, the formula

would look like this:

=IF(ISERROR(SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))),"",INDEX(A$2:

A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))))

As you can see, this makes the formula about twice as long and, if I was

still using the Sheet references, this would make it even longer! Long

formulas tend to "scare" people! Not only is the formula long but when the

error trap evaluates to FALSE (no error) the formula has to process the data

twice. So naturally, that takes twice as long.

I used a "psuedo" error trap that effectively does the same thing but is

much shorter to express and is more efficient:

=IF(ROWS($1:1)<=COUNTIF(B$2:B$8,"vac")

=IF(ISERROR(SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1)))

With the error trap I've used the formula only has to process the data once.

The logic is that you count the number of instances that meet the criteria:

COUNTIF(B$2:B$8,"vac")

Then compare that to the number of cells that the formula is being copied

to:

ROWS($1:1)

When you drag copy down to more cells the ROWS($1:1) function will increment

to $1:2, $1:3 etc. This is compared to COUNTIF(B$2:B$8,"vac") and based on

your posted example, COUNTIF(B$2:B$8,"vac") = 2. So, we end up with this:

=IF(1<=2,value_if_true,value_if_false)

=IF(2<=2,value_if_true,value_if_false)

=IF(3<=2,value_if_true,value_if_false)

etc

The value_if_true argument is:

INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))))

The value_if_false argument is: ""

Returns a blank cell instead of an error, #NUM!

Now, let's see what's happening when the value_if_true argument is met.

INDEX(A$2:A$8,SMALL(IF(B$2:B$8="vac",ROW(A$2:A$8)-ROW(A$2)+1),ROWS($1:1))))

Ok, there's an indexed range of values, INDEX(A$2:A$8, which are the dates.

There are a total of 7 elements in the range A$2:A$8. The Index function

holds these elements in a relative order. That order is the total number of

elements. There are 7 elements so the order is 1,2,3,4,5,6,7 where:

A2 = 1

A3 = 2

A4 = 3

...

A8 = 7

Now we need to tell the formula which elements of that range to return based

on meeting the criteria. That criteria is:

IF(B$2:B$8="vac"

This will return an array of TRUE's or FALSE's. Based on the posted example

that would be:

FALSE

FALSE

TRUE

FALSE

FALSE

TRUE

FALSE

Ok, the value_if_true argument is:

ROW(A$2:A$8)-ROW(A$2)+1

And the value_if_false argument is nothing. No value_if_false argument was

defined and when that happens the default return is FALSE. We'll see how

that comes into play later on.

Back to the value_if_true argument: ROW(A$2:A$8)-ROW(A$2)+1

Since the INDEX function has a total of 7 elements indexed (1,2,3,4,5,6,7),

we need a means of generating an array of numbers from 1 to 7 that

correspond to the indexed elements. That's where ROW comes in handy.

ROW(A$2:A$8) generates an array of 7 numbers but that array is 2,3,4,5,6,7,8

and that array does not correspond to the indexed array of 1,2,3,4,5,6,7. To

take care of that we subtract the offset then add 1: -ROW(A$2)+1

This is how that is processed in the formula:

2 - 2 +1 = 1

3 - 2 + 1 =2

4 - 2 + 1 =3

5 - 2 + 1 =4

...

8 - 2 + 1 =7

Now we have our array from 1 to 7 that correspond to the indexed array of 1

to 7.

There are other ways to generate that array but this is the most

"foolproof".

So, now we put this all together to generate yet another array:

If TRUE = ROW number, if FALSE = FALSE:

B2 = vac = FALSE = FALSE

B3 = vac = FALSE = FALSE

B4 = vac = TRUE = 3

B5 = vac = FALSE = FALSE

B6 = vac = FALSE = FALSE

B7 = vac = TRUE = 6

B8 = vac = FALSE = FALSE

That array is then passed to the SMALL function:

SMALL({F,F,3,F,F,6,F},ROWS($1:1))

As is, that evaluates to the first smallest value which is 3. When drag

copied down the ROWS function will increment to $1:2 for the second

smallest, $1:3 for the third smallest, etc. Since there is no third smallest

that would generate a #NUM! error but remember, we have that taken care of

using our "psuedo" error trap.

Putting it all together. When copied down this is what you get:

INDEX(A$2:A$8,3) = the 3 rd element of the indexed array = 1/4

INDEX(A$2:A$8,6) = the 6 th element of the indexed array = 1/7

INDEX(A$2:A$8,#NUM!) = "" (blank)

There you have it!

Biff