Match and Tables
 

Can anybody tell me what to do:

I have a calculated table {=TABLE(B6,C6)} in cells C22:H33 and use
this formula:

=MATCH(MAX(C22:H33),C22:H33) but it alwys returns N/A.

Is there another way to get the row number for the cell, containing
the maximum value?

Jan

Match and Tables
 

MATCH only operates on 1D vectors. C22:H33 is 2D.

=SUMPRODUCT((ROW(C22:H33)-ROW(C22)+1)*(C22:H33=MAX(C22:H33)))

This formula will work correctly only if MAX will appear only once in
the data.

HTH
Kostis Vezerides

On Nov 11, 2:31*pm, wrote:
Can anybody tell me what to do:

I have a calculated table {=TABLE(B6,C6)} in cells C22:H33 and use
this formula:

=MATCH(MAX(C22:H33),C22:H33) but it alwys returns N/A.

Is there another way to get the row number for the cell, containing
the maximum value?

Jan

Match and Tables
 

Hi,

Assuming this is the data in the range D5:E10

1 45
2 67
3 878
10 12
5 23
6 1

In F5, enter the following array formula
=IF(OR(D5:E5=MAX($D$5:$E$10)),MAX(D5:E5),""). Copy this formula down to
F10.

IN cell F12, enter the following formula
=MATCH(MAX(D5:E10),F5:F10,0)+COUNTBLANK($F$1:F4)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

wrote in message
...
Can anybody tell me what to do:

I have a calculated table {=TABLE(B6,C6)} in cells C22:H33 and use
this formula:

=MATCH(MAX(C22:H33),C22:H33) but it alwys returns N/A.

Is there another way to get the row number for the cell, containing
the maximum value?

Jan

Match and Tables
 

Thank you. I used your formula and it worked out fine.

Jan

vezerid wrote:
MATCH only operates on 1D vectors. C22:H33 is 2D.

=SUMPRODUCT((ROW(C22:H33)-ROW(C22)+1)*(C22:H33=MAX(C22:H33)))

This formula will work correctly only if MAX will appear only once in
the data.

HTH
Kostis Vezerides

On Nov 11, 2:31 pm, wrote:
Can anybody tell me what to do:

I have a calculated table {=TABLE(B6,C6)} in cells C22:H33 and use
this formula:

=MATCH(MAX(C22:H33),C22:H33) but it alwys returns N/A.

Is there another way to get the row number for the cell, containing
the maximum value?

Jan

Match and Tables
 

I used versrid's formula, but thanks anywe

Ashish Mathur wrote:
Hi,

Assuming this is the data in the range D5:E10

1 45
2 67
3 878
10 12
5 23
6 1

In F5, enter the following array formula
=IF(OR(D5:E5=MAX($D$5:$E$10)),MAX(D5:E5),""). Copy this formula down
to F10.

IN cell F12, enter the following formula
=MATCH(MAX(D5:E10),F5:F10,0)+COUNTBLANK($F$1:F4)


wrote in message
...
Can anybody tell me what to do:

I have a calculated table {=TABLE(B6,C6)} in cells C22:H33 and use
this formula:

=MATCH(MAX(C22:H33),C22:H33) but it alwys returns N/A.

Is there another way to get the row number for the cell, containing
the maximum value?

Jan

Match and Tables
 

I used vezerid's formula, but thanks anyway.

Jan

Ashish Mathur wrote:
Hi,

Assuming this is the data in the range D5:E10

1 45
2 67
3 878
10 12
5 23
6 1

In F5, enter the following array formula
=IF(OR(D5:E5=MAX($D$5:$E$10)),MAX(D5:E5),""). Copy this formula down
to F10.

IN cell F12, enter the following formula
=MATCH(MAX(D5:E10),F5:F10,0)+COUNTBLANK($F$1:F4)


wrote in message
...
Can anybody tell me what to do:

I have a calculated table {=TABLE(B6,C6)} in cells C22:H33 and use
this formula:

=MATCH(MAX(C22:H33),C22:H33) but it alwys returns N/A.

Is there another way to get the row number for the cell, containing
the maximum value?

Jan

Match and Tables
 

You are welcome

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Jan Kronsell" wrote in message
...
I used vezerid's formula, but thanks anyway.

Jan

Ashish Mathur wrote:
Hi,

Assuming this is the data in the range D5:E10

1 45
2 67
3 878
10 12
5 23
6 1

In F5, enter the following array formula
=IF(OR(D5:E5=MAX($D$5:$E$10)),MAX(D5:E5),""). Copy this formula down
to F10.

IN cell F12, enter the following formula
=MATCH(MAX(D5:E10),F5:F10,0)+COUNTBLANK($F$1:F4)


wrote in message
...
Can anybody tell me what to do:

I have a calculated table {=TABLE(B6,C6)} in cells C22:H33 and use
this formula:

=MATCH(MAX(C22:H33),C22:H33) but it alwys returns N/A.

Is there another way to get the row number for the cell, containing
the maximum value?

Jan