Finding certain value in a column

[DoubleZ]

In Excel 2007, in column A I have some cells that contain employee's names
and certain cells that don't. For instance, in A1 I have employee1, in cells
A2-A13 I have other stuff and in cell A14 I have employee2. In cell B1 i
have the following formula: =isnumber(match(A1,Employees,0)) and I have a
list with employee's names in it defined as "Employees". This is what I need
to do:

In cell C I would like to have only employee names. If the same row in
column A does not contain an employee then I would like to return the
employee above it. For instance, cell A13 does not contain an employee and
the closest employee above it is in cell A1 so in cell C13 I would like to
have "employee1". I can't seem to figure out how to do this. I would prefer
to do it without a macro if possible.

Thanks.

 

[akphidelt]

You can set up Employees as a range like

Employee Name----Some Random Number or whatever

In Column c put the formula

=If(iserror(vlookup(A2,employees,2,FALSE)),C1,A2)

So in cell C1 put =A1, then start the formula above in cell C2

What this does is check to see if the employee is in the Employees array.
You can probably use your match formula also. If the employee does not show
up then Take the employee name above it... if it does show up then take the
employee name in Column A

 

[DoubleZ]

Thanks Akphidelt. I think we were both overthinking this one. But your idea
helped me figure it out. In cell C1 I just have =A1, then in C2 I have
=if(B2=true,A2,C1). Then I just autofilled down the column and that did the
trick.

Thanks again!