Look up in large table?

[brjohnson]

Hi,

I have a series of 60 items in columns at the top of a table (columns ED-GK) and dates vertically down the left in column EC. On any given day 5 of them items are chosen and their names are displayed in the cells intersecting their column and the date row.

Is there a way for me set up a formula to auto pull what the 5 are on any given day?

For example: Could I input 1/8/2010 and get the names of the 5 items that had been ranked as the top 5 for that day and then change the date to 4/15/2011 and get that list of 5 to auto update?

Thanks!

 

[T. Valko]

Hi,

I have a series of 60 items in columns at the top of a table (columns ED-GK) and dates vertically down the left in column EC. On any given day 5 of them items are chosen and their names are displayed in the cells intersecting their column and the date row.

Is there a way for me set up a formula to auto pull what the 5 are on any given day?

For example: Could I input 1/8/2010 and get the names of the 5 items that had been ranked as the top 5 for that day and then change the date to 4/15/2011 and get that list of 5 to auto update?

Thanks!

Let's see if we understand this...

Let's assume the cell EC10 = 1/8/2010.

Somewhere on row 10, ED10:GK10, there will be 5 names that you want to extract? Will there be more than 5 names on this row? If so, how do we know which 5 names you want to extract? You said:

5 items that had been ranked as the top 5 for that day

How are these items ranked?

 

[brjohnson]

Thanks for the quick reply.

To address your points: somewhere in row 10 there will be 5 names - That is correct. It can however be more or less than 5 names, the exact number is dependent upon user input.

As for how they are ranked - I am ranking them elsewhere in the spreadsheet based on performance, and then populating the table based on those results.

The result could be expressed simply as:

1/8/2010 Ice Cream, Chocolate Sauce, Bananas, Cereal, Milk

Where the date is input in one cell and the formula throws the results in a cell separating with commas. If it easier to display the resulting items in separate cells that is fine as well.

 

[T. Valko]

Thanks for the quick reply.

To address your points: somewhere in row 10 there will be 5 names - That is correct. It can however be more or less than 5 names, the exact number is dependent upon user input.

As for how they are ranked - I am ranking them elsewhere in the spreadsheet based on performance, and then populating the table based on those results.

The result could be expressed simply as:

1/8/2010 Ice Cream, Chocolate Sauce, Bananas, Cereal, Milk

Where the date is input in one cell and the formula throws the results in a cell separating with commas. If it easier to display the resulting items in separate cells that is fine as well.

Ok, without a more specific explanation of how the items are ranked the best I can do is extract the first N items from the corresponding row.

Will that suffice?

 

[brjohnson]

That'd be great- so long as first N items can start at a spot within the row - as in not column A, but instead ED

 

[T. Valko]

That'd be great- so long as first N items can start at a spot within the row - as in not column A, but instead ED

Try this...

Book1
	EC	ED	EE	EF	EG	EH	EI	EJ
1	_	_	_	_	_	_	_	_
2	1/1/2011	A	B	C	D	E	F	G
3	1/2/2011	_	_	H	_	I	_	_
4	1/3/2011	_	_	_	J	_	_	K
5	1/4/2011	_	_	_	_	_	_	_
6	1/5/2011	_	L	M	_	N	O	P
7	_	_	_	_	_	_	_	_
8	_	_	_	_	_	_	_	_
9	_	_	_	_	_	_	_	_
10	1/3/2011	J	K
11	2	_	_	_	_	_	_	_
Sheet1

Enter this formula in EC11. This will return the count of records for the lookup date.

=COUNTA(INDEX(ED2:EJ6,MATCH(EC10,EC2:EC6,0),0))

Enter this array formula** in ED10. This will extract up to the first 5 items, from left to right, that correspond to the lookup date.

=IF(COLUMNS($ED10:ED10)>MIN($EC11,5),"",INDEX($ED2:$EJ6,MATCH($EC10,$EC2:$EC6,0),SMALL(IF(INDEX($ED2:$EJ6,MATCH($EC10,$EC2:$EC6,0),0)<>"",COLUMN($ED2:$EJ6)),COLUMNS($ED10:ED10))-COLUMN($ED2)+1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy across to EH10.

Since that formula is a bit complicated I'm including a sample file that demonstrates this.

ZZZbrjohnson.xls 16kb

http://cjoint.com/?AGwwaUwffh3

 

[brjohnson]

T Valko - just realized that I forgot to thank you for final response! Worked great and was exactly what I needed. Much appreciated!

 

[T. Valko]

T Valko - just realized that I forgot to thank you for final response! Worked great and was exactly what I needed. Much appreciated!

You're welcome. Thanks for the feedback!