Find first, second, third largest and return another cell on same row

[notexcellent]

Hi there,

First post to the forum. What a great resource this place is!

I am trying to return a value from a row, based on two things: 1) Whether it meets a text criteria in another column and 2) Whether it is the first, second, or third largest amount in another column. Badly explained! I've attached a spreadsheet to show what I mean.

In the example, I'd like to be able to return the unique reference number of the three largest Apple baskets, along with their corresponding amounts.

I hope someone can help. I understand the LARGE and IF functions, but can not fathom a way to make them work together to do what I need!

[Tony Valko]

Is it possible that there might be duplicate amount numbers?

Like:

Apples 10
Apples 8
Apples 8
Apples 8

If so, then your top 3 list may have more than 3 items in it as demonstrated above.

[benishiryo]

hi there, welcome to the forum. try these array formulas. in C22:
=LARGE(IF($C$5:$C$16="Apples",$D$5:$D$16),ROWS(C$22:C22))

in B22:

Please Login or Register  to view this content.

...both confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[notexcellent]

Tony,

Yes that's a good point. In that instance I'm not too fussed if it just returns one based on whether it's unique reference number is lower.

Ben,

Many thanks - I will try that now and report back.

[notexcellent]

Ben,

That works like a charm. Thanks a lot. Now for me to get my head around how it works

Thanks again.

[notexcellent]

Sorry - I have another issue.

When using the second formula you provided, I ideally just want to select an entire column (e.g. B:B) so that when new entries are added at the bottom of the data table, my "Top 3" table automatically updates. I appreciate in my example spreadsheet this wouldn't work as I've put the Top 3 Table under enough, but hypothetically imagine the Top 3 table is on another sheet.

Is this possible? As at the moment it uses the ROW function that selects a specific selection of rows and therefore wouldn't pick up new entries at the bottom.

[Tony Valko]

You can create a table and when you add/remove data the formulas will automatically be dynamic to the size of the table.

Are you familiar with using tables in Excel 2007 and later?

[notexcellent]

Hi Tony,

I've converted the data to a Table, and I've just read a tutorial on how to name tables and use references to tables and rows etc in formulas. Very useful to learn actually, so thanks!

However the problem I have now is that I can't get the aforementioned formulas to work when I refer to the table. Here's my bash at how it would work:

Please Login or Register  to view this content.

However this just returns '1' in the unique basket reference column.

I attach my spreadsheet to show this in practice.

Again thanks for walking me through this, it's going to be immensely useful once I crack it!

[benishiryo]

try:

Please Login or Register  to view this content.

i changed just the red portion

[notexcellent]

Works like a charm, thanks guys.

[Tony Valko]

Try it like this:

=INDEX(Table1[Unique Basket Reference],SMALL(IF(Table1[Type of Fruit]="Apples",IF(Table1[Amount]=H7,ROW(Table1[Unique Basket Reference]))),COUNTIF(H$7:H7,H7))-MIN(ROW(Table1[Unique Basket Reference]))+1)

Still array entered.