Row and Col Index of a cell with Max Value

[mario]

I have two worksheets.

Lets say work sheet "apple", has the foll. data

A | B
_______________
1 | Jack | 45
2 | Martha | 32
3 | Eddie | 28
4 | Joe | 56
5 | Mary | 64
6 | Susan | 88


In another work sheet how can I extract the row and colum index of cell with
maximum value. In other words what formula can I use (in another) worksheet
to know the cell index of column B in worksheet "apple".

Please provide your inputs.

Thanks

 

[Niek Otten]

Since you seem to be looking column B, the Column index is 2. Or did I misunderstand your question?

The Row index:

=MATCH(MAX(Sheet2!B:B),Sheet2!B:B,0)

Beware of duplicates.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have two worksheets.
|
| Lets say work sheet "apple", has the foll. data
|
| A | B
| _______________
| 1 | Jack | 45
| 2 | Martha | 32
| 3 | Eddie | 28
| 4 | Joe | 56
| 5 | Mary | 64
| 6 | Susan | 88
|
|
| In another work sheet how can I extract the row and colum index of cell with
| maximum value. In other words what formula can I use (in another) worksheet
| to know the cell index of column B in worksheet "apple".
|
| Please provide your inputs.
|
| Thanks
|
|

 

[Mike H]

you know the column your looking there

=MATCH(MAX(B1:B6),B1:B6,0)

Mike

 

[Niek Otten]

Or

=MATCH(MAX(Apple!B:B),Apple!B:B,0)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Since you seem to be looking column B, the Column index is 2. Or did I misunderstand your question?
|
| The Row index:
|
| =MATCH(MAX(Sheet2!B:B),Sheet2!B:B,0)
|
| Beware of duplicates.
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
||I have two worksheets.
||
|| Lets say work sheet "apple", has the foll. data
||
|| A | B
|| _______________
|| 1 | Jack | 45
|| 2 | Martha | 32
|| 3 | Eddie | 28
|| 4 | Joe | 56
|| 5 | Mary | 64
|| 6 | Susan | 88
||
||
|| In another work sheet how can I extract the row and colum index of cell with
|| maximum value. In other words what formula can I use (in another) worksheet
|| to know the cell index of column B in worksheet "apple".
||
|| Please provide your inputs.
||
|| Thanks
||
||
|
|