INDEX MATCH MAX formula

[willow1985]

I need help with a formula to help me match column C with A, find the max value in column B and return that value.

The formula would be in column D (see below image). These are the results I am hoping to get. Unfortunately there are letters mixed in with numbers so I am unsure how to get around this.

Thank you to anyone who can help.

 

[willow1985]

I know that you can return a max value with the match portion of an index match

=INDEX(B2:B500,MATCH(MAX(C2:C500),A2:A500,0))

But I was curious about the reverse and returning the MAX value of the index.

=INDEX(MAX(B2:B500),MATCH(C2,A2:A500,0)) **I know this is not a valid formula. Trying to explain my thought process a bit better...

 

[DanteAmor]

I get a #VALUE error.

Try this

{=INDEX($B$2:$B$500,MATCH(C2&"|"&MAX(IF($B$2:$B$500<>"",($A$2:$A$500=C2)*(IF(ISNUMBER($B$2:$B$500),$B$2:$B$500,CODE(LEFT($B$2:$B$500)))))),$A$2:$A$500&"|"&IF($A$2:$A$500<>"",IF(ISNUMBER($B$2:$B$500),$B$2:$B$500,CODE(LEFT($B$2:$B$500)))),0))}

 

[sandy666]

How about post#6 solution?

 

[willow1985]

As usual DanteAmor, thank you so much. This works perfectly:

=INDEX($B$2:$B$500,MATCH(C2&"|"&MAX(IF($B$2:$B$500<>"",($A$2:$A$500=C2)*(IF(ISNUMBER($B$2:$B$500),$B$2:$B$500,CODE(LEFT($B$2:$B$500)))))),$A$2:$A$500&"|"&IF($A$2:$A$500<>"",IF(ISNUMBER($B$2:$B$500),$B$2:$B$500,CODE(LEFT($B$2:$B$500)))),0))}

And thanks to the rest of you for all of your help as well, I appreciate all of your time.

Take care ?

 

[willow1985]

with Power Query
View attachment 5021

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Column1"}, {{"Max", each List.Max([Column2]), type anynonnull}})
in
    Group

Hi Sandy, I was looking to do this without a code or table. Just a formula to get me a quick result.
But thank you very much!

 

[sandy666]

sure
Have a nice day

 

[BlueHornet]

If you set up your data in Excel defined tables (Insert/Table; I'm not going to presume how much you already understand about that) then you shouldn't have to worry about more or less data, and I expect that Dante's formula (and maybe Snakechips' as well) would work fine with however much or little data was there, and without having to rewrite the formula to accommodate changes in the number of data rows.

 

[DanteAmor]

As usual DanteAmor, thank you so much. This works perfectly:

Again with pleasure. Thanks for the feedback