Formula to match and rank with an if statement

[sandman85]

Not sure what I'm trying to do will work. I have been able to return values using LARGE function and gets the rest of the values associated with the number from the data sheet. I now want to add a condition to the formula to only return values that match another criteria.

[TABLE="width: 614"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: center"]Rank[/TD]
[TD="align: center"]Date [/TD]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]Orders P/H[/TD]
[TD="align: center"]Best Sale[/TD]
[TD="align: center"]Staff Availability[/TD]
[TD="align: center"]Performance Rating[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]8/06/2015[/TD]
[TD="align: center"]1188[/TD]
[TD="align: center"]0.75[/TD]
[TD="align: center"]133.15[/TD]
[TD="align: center"]100.00[/TD]
[TD="align: center"]Optimal[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]13/05/2017[/TD]
[TD="align: center"]1157[/TD]
[TD="align: center"]0.70[/TD]
[TD="align: center"]117.69[/TD]
[TD="align: center"]100.00[/TD]
[TD="align: center"]Sub Optimal[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]12/05/2017[/TD]
[TD="align: center"]1155[/TD]
[TD="align: center"]0.70[/TD]
[TD="align: center"]123.68[/TD]
[TD="align: center"]100.00[/TD]
[TD="align: center"]Optimal[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]11/05/2017[/TD]
[TD="align: center"]1136[/TD]
[TD="align: center"]0.70[/TD]
[TD="align: center"]120.38[/TD]
[TD="align: center"]100.00[/TD]
[TD="align: center"]Optimal[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]14/05/2017[/TD]
[TD="align: center"]1134[/TD]
[TD="align: center"]0.68[/TD]
[TD="align: center"]115.18[/TD]
[TD="align: center"]98.40[/TD]
[TD="align: center"]Sub Optimal[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]16/05/2017[/TD]
[TD="align: center"]1131[/TD]
[TD="align: center"]0.68[/TD]
[TD="align: center"]115.11[/TD]
[TD="align: center"]100.00[/TD]
[TD="align: center"]Sub Optimal[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]15/05/2017[/TD]
[TD="align: center"]1129[/TD]
[TD="align: center"]0.68[/TD]
[TD="align: center"]114.66[/TD]
[TD="align: center"]100.00[/TD]
[TD="align: center"]Sub Optimal[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]10/05/2017[/TD]
[TD="align: center"]1129[/TD]
[TD="align: center"]0.68[/TD]
[TD="align: center"]116.67[/TD]
[TD="align: center"]98.26[/TD]
[TD="align: center"]Sub Optimal[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]17/05/2017[/TD]
[TD="align: center"]1121[/TD]
[TD="align: center"]0.68[/TD]
[TD="align: center"]116.19[/TD]
[TD="align: center"]100.00[/TD]
[TD="align: center"]Sub Optimal[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]18/05/2017[/TD]
[TD="align: center"]1119[/TD]
[TD="align: center"]0.68[/TD]
[TD="align: center"]119.16[/TD]
[TD="align: center"]100.00[/TD]
[TD="align: center"]Sub Optimal[/TD]
[/TR]
</tbody>[/TABLE]

I've been using the following formula to return and rank the revenue value and then adjusting it to return the corresponding date, order p/h, best sale, staff availability and performance rating.
=INDEX(Data!$D$2:$D$886,MATCH(LARGE(Data!$D$2:$D$886,$B4),Data!$D$2:$D$886,0))
I have tried to add an if statement to only return and rank values that have an optimal performance rating and can't figure it out!

Any help would be greatly appreciated.

Thanks.

Sandy

 

[C with no eyes]

Is rank in column B?

 

[sandman85]

Hi C with no eyes,

Rank is in column B

 

[sandman85]

 

[C with no eyes]

Don't worry I haven't abandoned you! I was helping someone else too and fell asleep last night. I'm having trouble with this one. I can do it with a couple of helper columns but it looks a bit messy.

Internet says it can be done with an array formula but I must admit that I don't have as much experience with them. http://blog.contextures.com/archives/2012/07/12/check-multiple-criteria-with-excel-index-and-match

 

[sandman85]

Thanks - I will try and further refine this with the info in the blog.
Mess won't bother me, I'll just hide it
You are awesome.