ID lookup for Max Value in a given 'category'

[thisisntwally]

Hi,

I am struggling to get this Index/Match/Large function to run as expected. It keeps pulling the wrong output, and I am struggling to figure out what is driving the error.

Could anyone take a look and tell my why I am an idiot?

THANKS!

[Legend Rubber]

what are you trying to do?

[thisisntwally]

look up the ID, within each category, that has the highest value. I've tried swapping in Large for Max, and I get the same results (ID of 101 for 2nd category, when it should be 105, and the formula working fine for the other 3)

[ChemistB]

Yes,
When you do MAX(($B$20:$B$34=G29)*($C$20:$C$34),0)
you'll get
MAX(1*1, 1*2, 1*3, 0) (from rows 24,25,26) which results in 3
MATCH(MAX(($B$20:$B$34=G29)*($C$20:$C$34),0),$C$20:$C$34,0)
Simplifes to MATCH(3,$C$20:$C$34,0)
and the first 3 that Excel encounters in Col C is in row 22 which equates to "101"

Next will figure out how to get the correct result

[thisisntwally]

Yes,
When you do MAX(($B$20:$B$34=G29)*($C$20:$C$34),0)
you'll get
MAX(1*1, 1*2, 1*3, 0) (from rows 24,25,26) which results in 3
MATCH(MAX(($B$20:$B$34=G29)*($C$20:$C$34),0),$C$20:$C$34,0)
Simplifes to MATCH(3,$C$20:$C$34,0)
and the first 3 that Excel encounters in Col C is in row 22 which equates to "101"

Next will figure out how to get the correct result

ah I see! I was thinking the problem might have been something like that, but obviously failed to follow the tracks effectively...

[ChemistB]

Arrayed function
In H10 dragged down

=INDEX($A$2:$A$16,LARGE(IF($B$2:$B$16=G10,ROW($B$2:$B$16)-1,0),1))

In H28
=INDEX($A$20:$A$34,LARGE(IF($B$20:$B$34=G28,ROW($B$20:$B$34)-19,0),1))
Does that work for you?

[thisisntwally]

Arrayed function
In H10 dragged down

=INDEX($A$2:$A$16,LARGE(IF($B$2:$B$16=G10,ROW($B$2:$B$16)-1,0),1))

In H28
=INDEX($A$20:$A$34,LARGE(IF($B$20:$B$34=G28,ROW($B$20:$B$34)-19,0),1))
Does that work for you?

It does! Now I need to parse what you're doing there...

THANKS!!

[thisisntwally]

Oh blarg, it breaks if ID is text. Which it is in real life. Example fail.

[ChemistB]

It shouldn't break if ID is text. Upload an example with it not working, please.

[thisisntwally]

It shouldn't break if ID is text. Upload an example with it not working, please.

it's only breaking when ID is Text & Category is #

[Teethless mama]

In H29: =INDEX(A$20:A$34,MATCH(1,(C$20:C$34=MAX(($B$20:$B$34=G29)*($C$20:$C$34),0))*(B$20:B$34=G29),0))

ctrl+shift+enter, not just enter

[ChemistB]

You did not enter your second set as an array and your spreadsheet is set to Manual calculation so you will need to hit F9 to have it calculate the correct results. Does that help?

[thisisntwally]

@chemistB

Perfect - Thanks!

Can you explain what you're doing with the if/row element? I am not grasping how you are referencing the largest Value.

[thisisntwally]

@chemistB

Perfect - Thanks!

Can you explain what you're doing with the if/row element? I am not grasping how you are referencing the largest Value.

Ah, so is the relient upon the Value column being sorted ascending?

[ChemistB]

Yes it is. I never compare anything to column C. If it is not going up then this becomes a bit more complex but is also doable.
Check mama's and Marcol's solutions. They reference Col C in their formulas.

[thisisntwally]

Yes it is. I never compare anything to column C. If it is not going up then this becomes a bit more complex but is also doable.
Check mama's and Marcol's solutions. They reference Col C in their formulas.

Your solution is elegant as hell, but yeah, i need to make sure we're referencing cell.

Mama & Marcol, you're on deck

[Marcol]

See if this workbook helps (non CSE formulae)

In H10

=INDEX($A$2:$A$16,MATCH(MAX(INDEX(($B$2:$B$16=G10)*($C$2:$C$16),,1)),INDEX(($B$2:$B$16=G10)*($C$2:$C$16),,1),0))

In H29

=INDEX($A$20:$A$34,MATCH(MAX(INDEX(($B$20:$B$34=G28)*($C$20:$C$34),,1)),INDEX(($B$20:$B$34=G28)*($C$20:$C$34),,1),0))

[ChemistB]

LARGE(IF($B$2:$B$16=G11,ROW($B$2:$B$16)-1,0),1)

Okay, what we're doing here is creating a subarray from the larger array of B2:B16, remember this has to return the relative row for the INDEX to locate the matching value in Col A.
IF(B2:B16=G11, ROW(B2:B16)-1,0) will return 0 if the value in B doesn't match the value in G10. If it does match it returns that row # -1 (we subtract 1 because we want a relative row number and this series starts in B2. Note in the second series which starts at B20, we subtract 19)

So, with your data Excel comes up with this array (in H11 for example)
{0,0,0,0,5,6,7,0,0,0,0,0,0,0} first match is in row 6 (minus 1 = 5) and so on

Then we have LARGE({0,0,0,0,5,6,7,0,0,0,0,0,0,0}, 1) the final 1 says find the largest number so that number is 7

=INDEX($A$2:$A$16, 7) which yields the value in A8
Clear as mud?