need help w/ corresponding cells?

[DeeZi]

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 wan
the A that corresponds to this which would be 3 (the max of B column i
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 advanc

 

[Dave Peterson]

How about...

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

That final 0 is very important.

 

[Alex]

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

 

[Chip Pearson]

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" <[email protected]>
wrote in message
news[email protected]...