Large and index

[rpadminserver]

Hello Friends...

I'm using the LARGE command to find the top 5 values in a column with an IF condition. This is working perfect.
=LARGE(IF(A:A="LEVEL 1",D:D,""),1)
(doing ctr-shift-enter for array)

Now, my problem, in column C I have a description that corresponds with the amount found in column D I'm trying to come up with something that will give me the description also

I've tried
=INDEX(C:C,LARGE(IF(A:A="HMOX",D:D,""),1),0)
(again, using array)

I'm pulling a description from C but not the right one.

Help Please.

 

[jasonb75]

Try as array (untested)

=INDEX(C:C,MATCH(LARGE(IF(A:A="LEVEL 1",D:D),1),D:D,0))

 

[rpadminserver]

PERFECT!!!!!!!

Thanks

 

[Aladin Akyurek]

Hello Friends...

I'm using the LARGE command to find the top 5 values in a column with an IF condition. This is working perfect.
=LARGE(IF(A:A="LEVEL 1",D:D,""),1)
(doing ctr-shift-enter for array)

Now, my problem, in column C I have a description that corresponds with the amount found in column D I'm trying to come up with something that will give me the description also

I've tried
=INDEX(C:C,LARGE(IF(A:A="HMOX",D:D,""),1),0)
(again, using array)

I'm pulling a description from C but not the right one.

Help Please.

That combination is flawed... Try to adapt from post #7 in:

http://www.mrexcel.com/forum/showthread.php?t=342541

 

[rpadminserver]

@Aladin,

Thanks for this idea...
I read the post you referenced, I wont have any dups. That was taken care of by the SQL that pulls my data.

 

[Aladin Akyurek]

@Aladin,

Thanks for this idea...
I read the post you referenced, I wont have any dups. That was taken care of by the SQL that pulls my data.

SQL, if well-formed, won't eliminate ties...

A,5
B,2
C,2
D,4
E,5

"Who has the max score" cannot yield any other result than: A and E.

 

[rpadminserver]

The Values in column D are counts done off of levels and descriptions that are grouped (using group by) in the query

So there will never be more then 1 "Level 1" and "Description 1". The counts are grouped by Level then By descriptions.

---
Actually I see what you are saying....
I'm dealing with high counts so, even though possible, I don't expect to have dup counts. But I'll keep an eye out for it. Thanks



Thanks

 

[Aladin Akyurek]

The Values in column D are counts done off of levels and descriptions that are grouped (using group by) in the query

So there will never be more then 1 "Level 1" and "Description 1". The counts are grouped by Level then By descriptions.

---
Actually I see what you are saying....
I'm dealing with high counts so, even though possible, I don't expect to have dup counts. But I'll keep an eye out for it. Thanks



Thanks

This is frustrating... I teach my students also relational db's and querying such db's using query grid of Access... Consider the following table:

DEPARMENTS(DID,Description,DHead,Budget)

where DID is the primary key.

The following question:

Which department has the highest budget?

should never be answered with a query that just expects
or designed to expect 1 department having such a budget.
I'd grade such a query definitely with nil.

The same in Excel... Solutions like:

=INDEX(ResultRange,MATCH(MAX(MatchRange),MatchRange,0))

is not admissible.

 

[rpadminserver]

Thanks for all the help, but I simplified the question I asked, and the scenario to get the answer I needed.

The project I'm working on is not directed toward the top 5 descriptions. The purpose of the report is to compare the percentage of the top 5 against the whole. So if there are any ties between 5-6-7-8, I only need 1 of those to show on the report to complete my objective. That is why equal counts are not an issue.

Thanks again for your help
R