using an IF or AND function to get next largest value in list

[djferrick]

Hi .

I have a list of numbers and a corresponding list of text values

Team Errors
Query Category 7
Query sub cat 8
MSN 1
Exchange date 17
Meter Type 1
Reads 0
Flows 0
MAP ID 3
SSC 3
Mtr type 2
Ecoes date 8

I have been using the below formula to get the top three values an
return the corresponding text string in a cell on another part of th
worksheet.

=INDEX(W8:X18,MATCH(LARGE(X8:X18,2),X8:X18,0),1)

But as you can see the 2nd largest value has two results. How do I ge
excel to print one of them and the next time to test wether that strin
has already been printed and go for the 'other' joint 2nd largest numbe
?

When I change the LARGE array argument from 2nd largest to 3rd larges
number it returns the same string i.e 'Query sub cat' - how can I get i
to test wether that string has already been printed and print the strin
'Ecoes date' ?

I assume I want to add an AND operator into the above formula somethin
like AND<= the text in cell F30. So look at cell F30 and see what tex
string is there . If it matches the result from the INDEX formula the
print the other string instance of the number / value

thanks for any and all hel

 

[Claus Busch]

Hi,

Am Mon, 11 Jun 2012 14:13:11 +0000 schrieb djferrick:

Team Errors
Query Category 7
Query sub cat 8
MSN 1
Exchange date 17
Meter Type 1
Reads 0
Flows 0
MAP ID 3
SSC 3
Mtr type 2
Ecoes date 8

But as you can see the 2nd largest value has two results. How do I get
excel to print one of them and the next time to test wether that string
has already been printed and go for the 'other' joint 2nd largest number
?

try:
=INDEX($W$8:$W$18,MATCH(LARGE(X$8:X$18-ROW($1:$11)/9^9,ROW(A1)),X$8:X$18-ROW($1:$11)/9^9,0))
and enter the formula with CTRL+Shift+Enter


Regards
Claus Busch