Lookup Problem

[CCripe]

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.

 

[T. Valko]

The easiest way to do this is to use a filter and filter on the year.

If you want a formula...

This range named table:

2007 CL 12 8
2007 PL 20 18
2006 CL 17 15
2006 PL 14 12

B8 = 2006

Enter this array formula** in C8 (?) and copy across to F8 then down until
you get blanks:

=IF(ROWS(C$8:C8)<=COUNTIF(Year,$B$8),INDEX(table,SMALL(IF(Year=$B$8,ROW(table)-MIN(ROW(table))+1),ROWS(C$8:C8)),COLUMNS($C8:C8)),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)