find largest values, then return corresponding row values.
 

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

find largest values, then return corresponding row values.
 

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

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=544848

find largest values, then return corresponding row values.
 

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.


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=544848

find largest values, then return corresponding row values.
 

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"
wrote in message
news:neurotypical.289zwz_1148413501.7214@excelforu m-nospam.com...

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

find largest values, then return corresponding row values.
 

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?


--
neurotypical
------------------------------------------------------------------------
neurotypical's Profile: http://www.excelforum.com/member.php...o&userid=34719
View this thread: http://www.excelforum.com/showthread...hreadid=544848

find largest values, then return corresponding row values.
 

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


--
neurotypical
------------------------------------------------------------------------
neurotypical's Profile: http://www.excelforum.com/member.php...o&userid=34719
View this thread: http://www.excelforum.com/showthread...hreadid=544848

find largest values, then return corresponding row values.
 

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.


--
neurotypical
------------------------------------------------------------------------
neurotypical's Profile: http://www.excelforum.com/member.php...o&userid=34719
View this thread: http://www.excelforum.com/showthread...hreadid=544848

find largest values, then return corresponding row values.
 

nevermind. I just found a fix for this.


--
neurotypical
------------------------------------------------------------------------
neurotypical's Profile: http://www.excelforum.com/member.php...o&userid=34719
View this thread: http://www.excelforum.com/showthread...hreadid=544848