Return of Cell value as result of =LARGE()

Posted by J Savery on September 07, 2001 9:35 AM

I would like to know what the syntax would be to retrieve the cell address affiliated with the results of =Large() as follows:

A B
name1 100
name2 150
name3 175

=Large(b1:b3,1) returns 175. How can I get the value of name3 as well, which is the name of the =Large() result ???

Thanks,

J Savery

Posted by Juan Pablo on September 07, 2001 9:44 AM

You could use this formula (Assuming the Large formula is in C1 of the same sheet, if not, change the match formula)

=INDEX($A$1:$B$3,MATCH(C1,$B$1:$B$3,0),1)

Also, if there are data that repeats (Meaning, two or more "large" numbers) the formula will return the first that it finds on the list.

Juan Pablo

Posted by Jerry on September 07, 2001 10:58 AM

Along those same lines:

A B F
1 ITEM1 ITEM2 8
2 ITEM3 ITEM2 9
3 ITEM2 ITEM4 10

How do I tell Excel to take the value in A3, look in Col B for Matching Items and then return the largest value from Col F? (Cols C-E have other data I don't want to compare in it.) The answer from the formula would be 9.

Posted by Aladin Akyurek on September 07, 2001 11:19 AM

Jerry,

Array-enter:

=IF(COUNTIF(B1:B3,A3)>0,LARGE(IF(B1:B3=A3,F1:F3),1),"")

In order to array-enter a formula, you need to hit CONTROL+SHIFT+ENTER at the same time, not just ENTER.

Aladin

=============

Posted by Jerry on September 10, 2001 5:17 AM

Thanks Aladin!