Using large function, and pulling adjacent data

[pds]

I am using the LARGE function to pull the highest number from a range...pretty simple. What I want to do is also pull the adjacent data from whatever was selected in the LARGE equation.


Example:

Alpha,3

Delta,2

Beta,1


If column 2 is the number range, the LARGE equation =LARGE(B1:B3,1) would pull "3" assuming I wanted the highest number. Where I get stuck is when the result is pulled, I want the adjacent cell to pull the "Alpha", or cell A1.


So if the LARGE equation was in another worksheet in Cell A1, I would want cell A2 to pull the data in range (A1:A3) that corresponded to what the LARGE equation pulled in A1.

 

[keymaster]

Hi PDS,


Welcome to Chandoo.org and thanks for your question.


You can use MATCH, INDEX to do this. In A2 write,


=INDEX(sheet1!A1:A3,match(A1,Sheet1!B1:B3,0))


This will find the large result in A1 in Sheet1 B1:B3 and return corresponding value from column A.


For more on this, refer to http://chandoo.org/wp/2010/11/02/how-to-lookup-values-to-left/

 

[pds]

Thanks I almost have it working. Only problem is the way I have it set up it does the following:


A,7

B,5

C,9

D,7

E,3


If in separate cells I use the LARGE function to create a list of the top three values from column B, and then the MATCH,INDEX function to pull the corresponding data from column A, when there are duplicate values (like the 7), it pulls the first corresponding value in column A for both (In this case "A"). My results look like this:


C,9

A,7

A,7


If the value of "D" in the original cells would change to 6, the results show correctly as


C,9

A,7

D,6

 

[Sajan]

Hi pds,

You would need to extract the large numbers while "remembering" the location of those numbers. One approach is to concatenate the numbers with their position, and then use the LARGE function.


For example, the following returns the string corresponding to the largest number:

=INDEX(ListStrg, ROWS(ListNum)+1-MOD(LARGE(ListNum+((ROWS(ListNum)-(ROW(ListNum)-ROW(INDEX(ListNum,1))+1)+1)%%%), ROW(1:1)),1)*10^6)

entered with Ctrl + Shift + Enter


Copy formula to additional rows to get the next string, etc. (The formula returns an error when there are no more strings to return. Please feel free to add error trapping as desired.)


(ListStrg is assumed to be the range of strings, and ListNum is assumed to be the range of numbers.)


In this case, the formula concatenates the location (in reverse order, as fractions) to the numbers in your list so that LARGE function works as expected. Then, the fraction is turned into the location in the list, so as to be able to find it in the list of strings.


Cheers,

Sajan.

 

[Faseeh]

Hi pds,


Besdies Sajan's formula you can use this one as well for below data (A1:B9):

[pre]

A	1
B	2
C	3
D	5
E	6
F	5
G	6
H	2
I	1[/pre]

[code]=INDEX($A$1:$A$9,MATCH(LARGE((($B$1:$B$9)+ROW($B$1:$B$9)/100000000),ROW(A1)),(($B$1:$B$9)+ROW($B$1:$B$9)/100000000),0),0)

And...


=INDEX($B$1:$B$9,MATCH(LARGE((($B$1:$B$9)+ROW($B$1:$B$9)/100000000),ROW(A1)),(($B$1:$B$9)+ROW($B$1:$B$9)/100000000),0),0)[/code]


Ctrl+Shift+Enter & Drag...


Some what similar to Sajan's Formula..


Regards,