help please on look ups

T

trance4eva

i have a question please. I extract the second highest value from a
column using the large function, but i need to display a corresponnding
cell in the same row as that cell. For example, if the second higest
value was found in cell e5, then i need the contents that is in its
adjacent cell of a5. Anyway of doing this? Thanks in advance.
 
P

Paul

trance4eva said:
i have a question please. I extract the second highest value from a
column using the large function, but i need to display a corresponnding
cell in the same row as that cell. For example, if the second higest
value was found in cell e5, then i need the contents that is in its
adjacent cell of a5. Anyway of doing this? Thanks in advance.
Click to expand...

Try this:
=INDEX(A1:A10,MATCH(LARGE(E1:E10,2),E1:E10,0))
Adjust the length of the ranges to suit your data.
 
K

Ken Wright

Yes. Use INDEX and MATCH, and then use the LARGE function to feed it the data to Match, eg:-

With your numeric data in say E5:E30, and your other data in A5:A30, the 2nd largest value would
be gained from:-

=LARGE($E$5:$E$30,2)

and you can then fold that into the following formula to feed the required MATCH argument which
will find how many rows down your data the match is, and then return the value from the same row
in Col A using the INDEX function.

=INDEX($A$5:$A$30,MATCH(LARGE($E$5:$E$30,2),$E$5:$E$30,0))
 
B

Bernard V Liengme

Hello,
Try =INDEX(A1:A20,MATCH(MAX(E1:E20),E1:E20))
Read from the inside: MAX what is the max value?; MATCH what row is that
in?; INDEX get the data from the same row but in another array)

Best wishes
Bernard
 
B

Bob Phillips

Assuming that the rows span 1-20, try this

=INDEX(A1:A20,LARGE(E1:E20,2))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
B

Bernard V Liengme

Of course, replace MAX(...) by (LARGE(...,2)
I overlooked 'second' largest.
Bernard

Bernard V Liengme said:
Hello,
Try =INDEX(A1:A20,MATCH(MAX(E1:E20),E1:E20))
Read from the inside: MAX what is the max value?; MATCH what row is that
in?; INDEX get the data from the same row but in another array)

Best wishes
Bernard
Click to expand...
 
B

Bernard V Liengme

Odd how we read the same question is two ways!
You use the second largest value to find result in A, I used its position.
Wonder what OP wants.
Bernard


Bob Phillips said:
Assuming that the rows span 1-20, try this

=INDEX(A1:A20,LARGE(E1:E20,2))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
Click to expand...
 
Top