Index match minifs

[martinijr]

Hi Everyone.

Could you please assist in identifying a formula that will work to convert the Data into the below Summary?

I am trying to find the minimum cost in each category + type and pull into a summary sheet along with the corresponding company name and code.

I've pasted my sheet below. The formula I am running in K5 (and dragged across and down) is =INDEX($D$4:$D$22,MATCH(MINIFS($C$4:$C$22,$A$4:$A$22,$H5,$B$4:$B$22,$I5),$C$4:$C$22,0))

But it is not working as for different categories that have the same minimum, index is unable to differentiate between the categories and pulls through the first one in the data.

I.e. the correct response should be Company 5 in K5 and Company 6 in K6 and similarly Code 5 in L5 and Code 6 in L6.

Thank you in advance.

 

[Fluff]

Hi & welcome to MrExcel.
In J4 copied down only try

=INDEX(SORT(FILTER($C$4:$E$22,($A$4:$A$22=H4)*($B$4:$B$22=I4)),1,1),1)

 

[martinijr]

Hi & welcome to MrExcel.
In J4 copied down only try

=INDEX(SORT(FILTER($C$4:$E$22,($A$4:$A$22=H4)*($B$4:$B$22=I4)),1,1),1)

Thanks for help Fluff!

 

[martinijr]

The solution that I found most effective was:
1. in column J to find the minimum:=MINIFS($C$4:$C$22,$A$4:$A$22,$H4,$B$4:$B$22,I4)
2. In Column K, use index,match for multiple of criteria. One of which was the solution to Column J. =INDEX(D$4:D$22,MATCH(1,INDEX(($H5=$A$4:$A$22)*($I5=$B$4:$B$22)*($J5=$C$4:$C$22),0,1),0))
3. In Column L, same as Column K: =INDEX(E$4:E$22,MATCH(1,INDEX(($H5=$A$4:$A$22)*($I5=$B$4:$B$22)*($J5=$C$4:$C$22),0,1),0))