Formula find max enter , return different column

ste33uka

Active Member
Joined
Jan 31, 2020
Messages
471
Office Version
  1. 365
Platform
  1. Windows
Hi would anyone have a formula to find the max of column 'c', then enter value from column 'a'
so in example pear would return as value

Book1
ABCDE
112
2
3
4
5pear7
6pear3
7apple5
8pear12
9orange6
10
11
12
13
Sheet1
Cell Formulas
RangeFormula
E1E1=MAX(C5:C10)
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Assuming there would not be duplicate max values in column C.
Book1
ABCDE
1pear
2
3
4
5pear7
6pear3
7apple5
8pear12
9orange6
Sheet1
Cell Formulas
RangeFormula
E1E1=INDEX($A$5:$A$9,MATCH(MAX($C$5:$C$9),$C$5:$C$9))
 
Upvote 0
Assuming there would not be duplicate max values in column C.
Book1
ABCDE
1pear
2
3
4
5pear7
6pear3
7apple5
8pear12
9orange6
Sheet1
Cell Formulas
RangeFormula
E1E1=INDEX($A$5:$A$9,MATCH(MAX($C$5:$C$9),$C$5:$C$9))

Thanks alot
 
Upvote 0
i dragged the formula for more cells and it gives wrong result
what am i missing ?
Book3
ABCDEF
1orange
2
3
4
5orange0
6apple0
7pear0
8apple18
9orange2
10orange2
11orange3
12apple3
13orange2
14orange4
15orange6
16orange1
17
18
Sheet1
Cell Formulas
RangeFormula
F1F1=INDEX($A$5:$A$16,MATCH(MAX($C$5:$C$16),$C$5:$C$16))
 
Upvote 0
with or without duplicates but with Power Query (Get&Transform)

Column1Column2Column3Custom
pear7pear
pear3apple
apple5
pear12
orange6
apple12

Rich (BB code):
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    IF = Table.AddColumn(Source, "Custom", each if [Column3] = List.Max(Source[Column3]) then [Column1] else null),
    FilterNull = Table.SelectRows(IF, each ([Custom] <> null)),
    TSC = Table.SelectColumns(FilterNull,{"Custom"})
in
    TSC
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top