showing the contents of dynamic cells

L

lost

If I use the following spread sheet as an example





a b c

1 23 2 34

2 22 23 75

3 3 45 32



I am trying to find the value of rows 1 and 2 but only for the column where
c row 3 is the highest.

I.e. in this example as 3b is the highest how I can show the values of b1
and b2

How ever it is important to remember that all the 9 values in this spread
sheet are dynamic.



Finding the max number in row 3 is easy =Max(a3:c3)



Thanks
 
M

Max

One way ..

Source table assumed in A1:C3

Select a 2 cell range, say, E1:E2
Put in the formula bar, array-enter (press CTRL+SHIFT+ENTER):
=OFFSET(A1:A2,,MATCH(MAX(A3:C3),A3:C3,0)-1)
E1:E2 will return the required values

In the event of ties in the maximum value within A3:C3,
E1:E2 will return the values from the 1st matched col (from the left)
 
M

Max

Select a 2 cell range, say, E1:E2

If instead of vertically within E1:E2,
we want the results horizontally, say, within F1:G1
Select F1:G1, put in the formula bar and array-enter:
=TRANSPOSE(OFFSET(A1:A2,,MATCH(MAX(A3:C3),A3:C3,0)-1))
 
R

Ron Rosenfeld

If I use the following spread sheet as an example





a b c

1 23 2 34

2 22 23 75

3 3 45 32



I am trying to find the value of rows 1 and 2 but only for the column where
c row 3 is the highest.

I.e. in this example as 3b is the highest how I can show the values of b1
and b2

How ever it is important to remember that all the 9 values in this spread
sheet are dynamic.



Finding the max number in row 3 is easy =Max(a3:c3)



Thanks

=SUM(OFFSET(A1,0,MATCH(MAX(A3:C3),A3:C3,0)-1,2))
Click to expand...

--ron
 
Top