INDEX MATCH MAX formula

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
871
Office Version
  1. 365
Platform
  1. Windows
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.

1580158292782.png
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Please upload the data instead of a photo. No one really wants to type all that in but we all want to help.

Use XL2BB app.

Also, tell us what Excel version you're using.
 
Upvote 0
The photo was just a visual example. I did not save the document and the original is confidential.
 
Upvote 0
Upload the data instead of the photo. The sample data above, the oranges and reds and stuff.
 
Upvote 0
Will certain 'colours' always be with numbers and others with letters?
Roughly How many 'colours'?
How high might the numbers go?
Will the letters extend beyond A-Z ?
Are you averse to using 'helper columns' if necessary?
What is the typical extent of the data ? eg columns rows
 
Upvote 0
with Power Query
grp.png

Code:
// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Column1"}, {{"Max", each List.Max([Column2]), type anynonnull}})
in
    Group
 
Upvote 0
Try this array formula

Book1
ABCD
1ColorValueColorMax
2Blue5Blue5
3Red0Red4
4Red4Yellow3
5Blue1Green0
6Yellow1OrangeD
7Green0
8OrangeA
9Red2
10Yellow3
11OrangeD
sheet
Cell Formulas
RangeFormula
D2:D6D2{=INDEX($B$2:$B$11,MATCH(C2&"|"&MAX(($A$2:$A$11=C2)*(IF(ISNUMBER($B$2:$B$11),$B$2:$B$11,CODE(LEFT($B$2:$B$11))))),$A$2:$A$11&"|"&IF(ISNUMBER($B$2:$B$11),$B$2:$B$11,CODE(LEFT($B$2:$B$11))),0))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 
Upvote 0
Within the bounds of your example, at least.......

Book1
ABCD
1Blue5Blue5
2Red0Red4
3Red4Yellow3
4Blue1Green0
5Yellow1OrangeD
6Green0WhiteStripes 
7OrangeA
8Red2
9Yellow3
10OrangeD
Sheet6
Cell Formulas
RangeFormula
D1:D6D1{=IFERROR(CHAR(MAX(CODE($B$1:$B$10)*($A$1:$A$10=C1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.


Whether it's scalable ?
 
Upvote 0
Try this array formula

Book1
ABCD
1ColorValueColorMax
2Blue5Blue5
3Red0Red4
4Red4Yellow3
5Blue1Green0
6Yellow1OrangeD
7Green0
8OrangeA
9Red2
10Yellow3
11OrangeD
sheet
Cell Formulas
RangeFormula
D2:D6D2{=INDEX($B$2:$B$11,MATCH(C2&"|"&MAX(($A$2:$A$11=C2)*(IF(ISNUMBER($B$2:$B$11),$B$2:$B$11,CODE(LEFT($B$2:$B$11))))),$A$2:$A$11&"|"&IF(ISNUMBER($B$2:$B$11),$B$2:$B$11,CODE(LEFT($B$2:$B$11))),0))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Hi DanteAmor,

This works perfect however if I wanted to increase the rows from 11 to 500 I get a #VALUE error. I could not post the original sheet due to confidential information but I have roughly 350 rows but wanted to cover 500 to be safe.

Thank you so much.

Carla
 
Upvote 0
Within the bounds of your example, at least.......

Book1
ABCD
1Blue5Blue5
2Red0Red4
3Red4Yellow3
4Blue1Green0
5Yellow1OrangeD
6Green0WhiteStripes 
7OrangeA
8Red2
9Yellow3
10OrangeD
Sheet6
Cell Formulas
RangeFormula
D1:D6D1{=IFERROR(CHAR(MAX(CODE($B$1:$B$10)*($A$1:$A$10=C1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.


Whether it's scalable ?

Almost all cells returned blanks with this formula even though there were matches
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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