need help w/ corresponding cells?
 

Sorry, first-timer here.

ex:

A B
1 2
2 5
3 8
4 3
5 1

if I want to display the max of B, I would do =max(B1:B5). But I want
the A that corresponds to this which would be 3 (the max of B column is
8, corresponding to 3 from A column).

I tried :

=INDEX(A2:A6,MATCH(MAX(B2:B6),B2:B6))

but this doesn't work on other formulas (if my B column were formulas).
Does anyone know how to get it to work w/ formulas?

Thanks in advance


--
DeeZi
------------------------------------------------------------------------
DeeZi's Profile: http://www.excelforum.com/member.php...o&userid=24805
View this thread: http://www.excelforum.com/showthread...hreadid=383637

How about...

=INDEX(A2:A6,MATCH(MAX(B2:B6),B2:B6,0))

That final 0 is very important.

DeeZi wrote:

Sorry, first-timer here.

ex:

A B
1 2
2 5
3 8
4 3
5 1

if I want to display the max of B, I would do =max(B1:B5). But I want
the A that corresponds to this which would be 3 (the max of B column is
8, corresponding to 3 from A column).

I tried :

=INDEX(A2:A6,MATCH(MAX(B2:B6),B2:B6))

but this doesn't work on other formulas (if my B column were formulas).
Does anyone know how to get it to work w/ formulas?

Thanks in advance

--
DeeZi
------------------------------------------------------------------------
DeeZi's Profile: http://www.excelforum.com/member.php...o&userid=24805
View this thread: http://www.excelforum.com/showthread...hreadid=383637

--

Dave Peterson

DeeZi

(1) No need to apologise for trying to learn.
(2) Not straightforward for a first timer


This will work

=INDEX(A1:B5,MATCH(MAX(B1:B5),B1:B5,0),1)

The MAX(B1:B5) gets the maximum value [I assume values are in range A1:B5]
The MATCH(MAX(B1:B5),B1:B5,0) finds the row number of the max value within
the column B1:B5. The '0' denotes an exact match type.
The INDEX bit looks in the array A1:B5 to find the row number you specify
(done by the MATCH formula) and then picks the vlaue from column 1. This is
the '1' at the end of the formula.


Regards


Alex


"DeeZi" wrote:


Sorry, first-timer here.

ex:

A B
1 2
2 5
3 8
4 3
5 1

if I want to display the max of B, I would do =max(B1:B5). But I want
the A that corresponds to this which would be 3 (the max of B column is
8, corresponding to 3 from A column).

I tried :

=INDEX(A2:A6,MATCH(MAX(B2:B6),B2:B6))

but this doesn't work on other formulas (if my B column were formulas).
Does anyone know how to get it to work w/ formulas?

Thanks in advance


--
DeeZi
------------------------------------------------------------------------
DeeZi's Profile: http://www.excelforum.com/member.php...o&userid=24805
View this thread: http://www.excelforum.com/showthread...hreadid=383637

Try

=INDEX(A2:A6,MATCH(MAX(B2:B6),B2:B6,0))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"DeeZi"
wrote in message
...

Sorry, first-timer here.

ex:

A B
1 2
2 5
3 8
4 3
5 1

if I want to display the max of B, I would do =max(B1:B5). But
I want
the A that corresponds to this which would be 3 (the max of B
column is
8, corresponding to 3 from A column).

I tried :

=INDEX(A2:A6,MATCH(MAX(B2:B6),B2:B6))

but this doesn't work on other formulas (if my B column were
formulas).
Does anyone know how to get it to work w/ formulas?

Thanks in advance


--
DeeZi
------------------------------------------------------------------------
DeeZi's Profile:
http://www.excelforum.com/member.php...o&userid=24805
View this thread:
http://www.excelforum.com/showthread...hreadid=383637