find largest values, then return corresponding row values.

**neurotypical** · 05-23-2006, 03:43 PM

I have a large table that is many columns. I am trying to create a 3 column "subtable" to condense data that will find the largest values in the third column of the main table, then return the corresponding row values from the first two columns of the main table. I

I can't do a lookup table because I am dealing with the third column, not the first.

I know how to use the LARGE function to return the largest values.

How do return the corresponding values from my first two columns?

I tried using something like this expression I found online, but it doesn't seem to be appropriate or I am not making the right tweaks.

=INDEX($A$1:$B$7,LARGE(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2)

MANY THANKS FOR YOUR HELP AND THIS FORUM!!!!

**mrice** · 05-23-2006, 03:45 PM

An easy way to get around this probelm is to create a copy of the third column (linked by formula) and place it just before the first column. You can then use VLOOKUP.

**mrice** · 05-23-2006, 03:46 PM

An easy way to get around this problem is to create a copy of the third column (linked by formula) and place it just before the first column. You can then use VLOOKUP.

**neurotypical** · 05-23-2006, 04:07 PM

mrice- thanks for the suggestion, but my values in column 3 aren't and can't be put in ascending order (they are results of a solver optimization and vary as I adjust other values). VLOOKUP won't work since they are not in ascending order. Any other suggestions?

Bob Phillips · 05-23-2006, 04:10 PM

Use this formula

=INDEX(A$1:A$7,MATCH(LARGE($C$1:$C$7,ROW(A1)),$C$1:$C$7,0))

and copy down and across

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"neurotypical" <[email protected]>
wrote in message
news:[email protected]...
>
> I have a large table that is many columns. I am trying to create a 3
> column "subtable" to condense data that will find the largest values in
> the third column of the main table, then return the corresponding row
> values from the first two columns of the main table. I
>
> I can't do a lookup table because I am dealing with the third column,
> not the first.
>
> I know how to use the LARGE function to return the largest values.
>
> How do return the corresponding values from my first two columns?
>
> I tried using something like this expression I found online, but it
> doesn't seem to be appropriate or I am not making the right tweaks.
>
> =INDEX($A$1:$B$7,LARGE(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2)
>
> MANY THANKS FOR YOUR HELP AND THIS FORUM!!!!
>
>
> --
> neurotypical
> ------------------------------------------------------------------------
> neurotypical's Profile:
http://www.excelforum.com/member.php...o&userid=34719
> View this thread: http://www.excelforum.com/showthread...hreadid=544848
>

**neurotypical** · 05-23-2006, 04:24 PM

that formula you just provided is working perfectly. Thanks Bob and mrice.

**neurotypical** · 05-24-2006, 04:47 PM

Okay- the formula Bob Phillips kindly provided was working well for me *except* when the two values in column C are equal. In those instances my "subtable" (for lack of a better term), returns the highest value and its associated cells twice, instead of both the highest values.

Any other suggestions? maybe I am approaching this problem the wrong way.