For finding the second largest you can use the formula LARGE() function
instead of MAX (). = LARGE(A1:A10,2) will give you the second largest..

I tried the formula and it works with the data in A1 : E4; something like
the below
Item Comp 1 Comp 2 Comp 3 Comp 4
Plastic 3 2 1 4
Wood 2 5 6 1
Item 3 3 2 2 1

In A11 = Wood
B11 = MAX(INDIRECT("B" & MATCH(A11,$A$1:$A$10,0) & ":J" &
MATCH(A11,$A$1:$A$10,0)))

However using INDEX is the easiest one..with out repeating MAX
For getting totals of both for example A11=Wood A12=Plastic you can add the
same formula with reference to A12.

=MAX(INDEX($B$1:$E$4,MATCH(A11,$A$1:$A$4,0),0))+MA X(INDEX($B$1:$E$4,MATCH(A12,$A$1:$A$4,0),0))


If this post helps click Yes
---------------
Jacob Skaria


"art" wrote:

Something is wrong with this formula, it found the wrong price.

"Jacob Skaria" wrote:

Assuming you have data in A1:J10 with first row with headers the below will
return the maximum value of the item..Companies from Col B to Col J.

A11 = Plastic

=MAX(INDIRECT("B" & MATCH(A11,A1:A10,0) & ":J" & MATCH(A11,A1:A10,0)))


If this post helps click Yes
---------------
Jacob Skaria


"art" wrote:

Hello All:

I would like to know how can I find out the folowing. I have a chart like
this:

Type Company 1 Company 2 Company 3
Product
Plastic 3.69 0.79 0.89
Metal 0.99 2.57 1.99
Wood 1.69 0.89 0.99

If I type in a cell A1 Plastic, then then cell A2 should tell me the highest
price found in the row for plastic.

I also want to be able to type plastic in A1 and then in B1 wood and in cell
A2 should calculate the total highest price for both.

Thanks in advance for any help.