I have a list of years belonging to different depts with numbers for internal
sales goals. I want to have a variable field to enter the correct year and
have it display the dept names and numbers for that year. Example:

A B C D
Year Dept Plan Actual
1 2007 CL 12 8
2 2007 PL 20 18
3 2006 CL 17 15
4 2006 PL 14 12

I want to enter a year in the variable cell ("2007") and have it return the
information pertaining to that year in some adjacent columns.


Year Dept Plan Actual
1 2007 CL 12 8
2 2007 PL 20 18


I tried putting in the following formula in each cell in the adjacent columns:
=INDEX($B$15:$F$35,INDEX(IF($B$15:$B$35=$B$8,ROW($ B$15:$B$35)),ROW(1:1)),3).

I copied the formula to all of the cells necessary to display each dept
name, plan and actual. It works for one year only. If I change the variable
cell to 2006, all works. If I try a different year, I get #REF! errors.