MatchIndex for Multiple Criteria and with Duplicate Values

[TopUser]

Hello Team!

I am new to the forum though i use to regularly get help from the posts as a guest. I am hoping a question of mine would be solved

I have a table with lot of data of opportunities in Pipeline from different departments in the organization, i want to get the Top 10 values based on the criteria i select for eg: Outcome-Win, Department-Healthcare (data validation drop downs), i have got a way to generate the Top Values using the below formula and it works well. The question here is I am not able to get the related fields attached to the value. The TotalValue is towards extreme right of the table and same sales value may be generated to different outcome or departments.

=SUMPRODUCT(LARGE((Table111[Department]=$B$6)*(Table111[Outcome]=$B$20)*(Table111[TotalValueOfSale]),1))

[TABLE="width: 1216"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 1216"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 7, align: center"]Top 10[/TD]
[/TR]
[TR]
[TD]Top[/TD]
[TD]Opportunity ID[/TD]
[TD]Client Name[/TD]
[TD]Opportunity SSL[/TD]
[TD]Status[/TD]
[TD]Outcome[/TD]
[TD] Sales Value[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ABC1[/TD]
[TD] to be generated [/TD]
[TD] to be generated[/TD]
[TD] to be generated[/TD]
[TD] to be generated[/TD]
[TD] $ 1,252,512[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ABC2[/TD]
[TD] based on sales value[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] $ 835,008[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]ABC3[/TD]
[TD] generated[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] $ 417,504[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]ABC4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] $ 417,504[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]ABC5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] $ 347,920[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]ABC6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] $ 347,920[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]ABC7[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] $ 278,336[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]ABC8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] $ 278,336[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]ABC9[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] $ 208,752[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]ABC10[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] $ 208,752[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[TopUser]

Hello All,

Wanted to confirm that even Opportunity ID has to be generated from the sales value, i just noticed it and apologies for the same.