LinkBack URL
About LinkBacks
HI there,
I want to lookup the largest, 2nd...5th largest value from a list by subgroup (Resource group)
The subgroups are not sorted in list.
Please see the attached example for details.
Many thanks
HI there,
I want to lookup the largest, 2nd...5th largest value from a list by subgroup (Resource group)
The subgroups are not sorted in list.
Please see the attached example for details.
Many thanks
Try this
Using Naming Convention
=LARGE(IF(Resources=E$4,Amounts),E5)
OR
Using Cells Naming Convention
=LARGE(IF($A$5:$A$39=E$4,$C$5:$C$39),E5)
- This is an array formula so you'll have to press simultaneously SHIFT + CTRL + ENTER keys to activate formula
- Drag down formula down to last entry
- Copy & Edit formula for the other two resource groups
see attachment
Last edited by Syrkrasi; 09-22-2017 at 01:55 PM.
Hi,
thank you very much!
What do I need to add to this formula in order to make it retrieve
the amounts per resource group X, Z, F by searching for the amounts in column RU instead of auxiliary column Resource group?
I changed the formula to =LARGE(IF($A$5:$A$39=E$4&"*";$C$5:$C$39);E5), but I only get #NUM.
Many thanks!
Hi pvp
You can do this problem using a Pivot Table if you use the Resource as a filter and then use the Top 5 values to show and sort the Pivot from large to small. See the attached for the answer. Note - no formulas required, just a little Pivot Magic!
PT top 5 filtered.xlsx
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Edit I failed to mention that due to regional settings you will have to change the argument separators from "," to ";". My apologies.
Try this array formula. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.Formula:
Please Login or Register to view this content.
Formula:Please Login or Register to view this content.
Last edited by FlameRetired; 09-23-2017 at 01:33 PM.
Dave
To get the RU you can use another array formula: INDEX & MATCHOriginally Posted by pvp
Using Naming Convention
=INDEX(RU,MATCH(G5,AMOUNTS,0))
OR
Using Cells Naming Convention
=INDEX($B$5:$B$39,MATCH(G5,$C$5:$C$39,0))
- This is an array formula so you'll have to press simultaneously SHIFT + CTRL + ENTER keys to activate formula
- Drag down formula down to last entry
- Copy & Edit formula for the other two resource groups
See attachment
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks