Lookup most recently entered (bottom) value

[danhodges99]

Hi,

I need to retrieve the most recently entered data (in the bottom row), if it matches a certain condition. Example:

ROW SPORT VIEWERS
1 Football 16000
2 Rugby 8000
3 Football 12000
4 Tennis 5000
5 Athletics 500
6 Football 13500
7 Tennis 4500

I basically need a way of saying "Return the most recent number of viewers for a particular sport (selected via a dropdown menu). For example, someone selects "Football" gets returned 13500, "Tennis" gets 4500.

Would be grateful for help, been trying for a while and quite stuck!

 

[Chris Mack]

Is there a page of raw data or something? I can't picture what you're asking for.

 

[CodeNinja]

Assuming you have Excel 2010 + you can use index / Aggregate...

Lets assume your above example is range A1:B8...
[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]ROW[/TD]
[TD]COL A[/TD]
[TD]COL B[/TD]
[TD]COL C[/TD]
[TD]COL D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]SPORT[/TD]
[TD]Viewers[/TD]
[TD]Select:[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Football[/TD]
[TD]16000[/TD]
[TD]Football[/TD]
[TD]13500[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Rugby[/TD]
[TD]8000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Football[/TD]
[TD]12000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Tennis[/TD]
[TD]5000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Athletics[/TD]
[TD]500[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Football[/TD]
[TD]13500[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]Tennis[/TD]
[TD]4500[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


You can use the following formula:
=INDEX($B$2:$B$8,AGGREGATE(14,6,(ROW($B$2:$B$8)-ROW($B$2)+1)/--($A$2:$A$8=$C$2),1))

If you do not have 2010, let me know and I will post an array formula you can use with prior versions

CN

 

[danhodges99]

Thanks CN - I do have 2010. It works perfectly for a table laid out as the example, but I'm having trouble substituting in references to a defined table with column and table names.
In my actual table, the table name is 'Main' and there are several columns, and I need four formulas (providing the most recent entry for each, dependant upon the sport desired). Column names are shown below. Apologies if this makes a difference, I assumed I could just substitute in.
Can you offer any guidance on how to do this please?

Here's my actual table, called 'Main':
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Sport[/TD]
[TD]ViewersA[/TD]
[TD]ViewersB[/TD]
[TD]ViewersC[/TD]
[TD]ViewersD[/TD]
[/TR]
[TR]
[TD]Football[/TD]
[TD]10000[/TD]
[TD]1[/TD]
[TD]10[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Rugby[/TD]
[TD]2000[/TD]
[TD]2[/TD]
[TD]20[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]Football[/TD]
[TD]25000[/TD]
[TD]3[/TD]
[TD]30[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]Tennis[/TD]
[TD]400[/TD]
[TD]4[/TD]
[TD]40[/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD]Athletics[/TD]
[TD]200[/TD]
[TD]5[/TD]
[TD]50[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]Tennis[/TD]
[TD]1000[/TD]
[TD]6[/TD]
[TD]60[/TD]
[TD]600[/TD]
[/TR]
[TR]
[TD]Football[/TD]
[TD]2500[/TD]
[TD]7[/TD]
[TD]70[/TD]
[TD]700[/TD]
[/TR]
[TR]
[TD]Rugby[/TD]
[TD]5600[/TD]
[TD]8[/TD]
[TD]80[/TD]
[TD]800[/TD]
[/TR]
[TR]
[TD]Football[/TD]
[TD]14500[/TD]
[TD]9[/TD]
[TD]90[/TD]
[TD]900[/TD]
[/TR]
</tbody>[/TABLE]

 

[CodeNinja]

Here is the table nomenclature version of the same formula... using your table. I put the formula in cell B14 referencing A14, so if you change A14 to Football, the answers change. Copy the formula over as I used both absolute and relative references. The formula assumes your table takes up A1:E11... If you change that, you will have to change the "-Row($B$2)" part.


Here's my actual table, called 'Main':
[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]Sport
[/TD]
[TD]ViewersA[/TD]
[TD]ViewersB[/TD]
[TD]ViewersC[/TD]
[TD]ViewersD[/TD]
[/TR]
[TR]
[TD]Football[/TD]
[TD]10000[/TD]
[TD]1[/TD]
[TD]10[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Rugby[/TD]
[TD]2000[/TD]
[TD]2[/TD]
[TD]20[/TD]
[TD]200
[/TD]
[/TR]
[TR]
[TD]Football[/TD]
[TD]25000[/TD]
[TD]3[/TD]
[TD]30[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]Tennis[/TD]
[TD]400[/TD]
[TD]4[/TD]
[TD]40[/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD]Athletics[/TD]
[TD]200[/TD]
[TD]5[/TD]
[TD]50[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]Tennis[/TD]
[TD]1000[/TD]
[TD]6[/TD]
[TD]60[/TD]
[TD]600[/TD]
[/TR]
[TR]
[TD]Football[/TD]
[TD]2500[/TD]
[TD]7[/TD]
[TD]70[/TD]
[TD]700[/TD]
[/TR]
[TR]
[TD]Rugby[/TD]
[TD]5600[/TD]
[TD]8[/TD]
[TD]80[/TD]
[TD]800[/TD]
[/TR]
[TR]
[TD]Football[/TD]
[TD]14500[/TD]
[TD]9[/TD]
[TD]90[/TD]
[TD]900[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rugby
[/TD]
[TD]5600[/TD]
[TD]8[/TD]
[TD]80[/TD]
[TD]800[/TD]
[/TR]
</tbody>[/TABLE]


=INDEX(Main[ViewersA],AGGREGATE(14,6,(ROW(Main[ViewersA])-ROW($B$2)+1)/(--(Main[[Sport]:[Sport]]=$A$14)),1))

Put that formula in cell B14 and drag/copy to C,D,and E14.

Good luck,

CN

 

[danhodges99]

Thanks CodeNinja - superb and works brilliantly