excel formula to choose the largest value for cell 1

[tinkode]

Hello kings of the formula

i need a formula to choose the largest value of the cell 1

 

[Fluff]

Try

Excel 2013/2016
	A	B
2	01	0
3	02	0
4	03	15
5	04	6
6	05	1
7	06	2
8	07	3
9	08	3
10	09	3
11	10	3
12	11	3
13	12	3
14	13	3
15	14	3
16	15	3
17	16	3
18	17	3
19	18	3
20	19	3
21
22		03
vor

Cell Formulas
Range	Formula
B22	=INDEX(A2:B20,MATCH(LARGE(B2:B20,1),B2:B20,0),1)

 

[Aladin Akyurek]

What must happen if we also got a 15 corresponding to 09?

 

[mumps]

Place this formula in the cell where you want the value returned: =INDEX(A2:A20,MATCH(MAX(B2:B20),B2:B20,0))
The formula assumes your data is in columns A and B.

 

[tinkode]

thansk you all

 

[Fluff]

You're welcome

 

[paldob]

You could just add this function to create your own formula

Function OffsetMax(Rng As Range)


PrevCol = 0
XVal = 0


For Each X In Rng
    
    If X > XVal Then
    
        XVal = X
        PrevCol = X.Offset(0, -1).Value
        
    End If
    
Next


OffsetMax = PrevCol


End Function

And then use =OffsetMax(B3:B20) <-- example, the B3:B20 will be replaced by your right hand column range. This will display the Offset 0, -1 of the largest value.

In no way do I consider myself an excel expert. The above function is pretty nifty to use I guess. It took me a while to get the Index + Match formulas into my brain. This is something I have just thought of from the top of my head. It's always fun to experiment though