Index Max the xth element to VBA

[pchatziko]

Hello i have the following problem

In the first row i have a list of elements and at subsequent rows random numbers under each element and i want to match the element with the max of each row, all the max values though not just the first one. This is achieved by the following function =IF(COLUMNS(F2:$F2)<=$E2,INDEX($A$1:$C$1,SMALL(IF($A2:$C2=$D2,COLUMN($A2:$C2)),COLUMNS(F2:$F2))),"") and dragged to the right the desired result is shown in the yellow cells

The following code is giving a type mismatch error and my vba is not so deep to understand why.

For i=2 to something

For j=1 to 3

Cells(count, j + 8) = WorksheetFunction.Index(Range(Cells(1, 1), Cells(1, 3)), WorksheetFunction.Small(IIf(Range(Cells(i, 1), Cells(i, 3)).Value = Cells(i, 4), Range(Cells(i, 1), Cells(i, 3)).Column, ""), Range(Cells(count, j)).Column))

Next j

Next i

Can you please fix the previous code or suggest anything that does the same thing?

Thanks a lot

 

[pchatziko]

Small typo in the above code
cells(i,j+8)=... not cells(count,j+8)

 

[Fluff]

Try changing the IIf to If

 

[pchatziko]

Try changing the IIf to If

It doesn't compile syntax error

 

[Fluff]

As you are using 365 how about

For i = 2 To something
   A = Evaluate("Filter(A1:C1," & Range("A" & i).Resize(, 3).Address & "=" & Range("D" & i).Address & ")")
   Cells(i, 6).Resize(, UBound(A)).Value = A

Next i

 

[pchatziko]

To be fair i can't fully grasp the solution but it works like a charm. Thank you

 

[Fluff]

It's basically just using the Xl filter function, which does the same as your formula

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1	a	b	c
2	4	5	5	5	2	b	c
3	8	5	8	8		a	c
Main

Cell Formulas
Range	Formula
F2:G3	F2	=FILTER($A$1:$C$1,A2:C2=D2)
Dynamic array formulas.

 

[pchatziko]

Yeap got it just wasn't aware of this. Thanks very much

 

[Fluff]

You're welcome & thanks for the feedback.