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.

Rank	Date	Revenue	Orders P/H	Best Sale	Staff Availability	Performance Rating
1	8/06/2015	1188	0.75	133.15	100.00	Optimal
2	13/05/2017	1157	0.70	117.69	100.00	Sub Optimal
3	12/05/2017	1155	0.70	123.68	100.00	Optimal
4	11/05/2017	1136	0.70	120.38	100.00	Optimal
5	14/05/2017	1134	0.68	115.18	98.40	Sub Optimal
6	16/05/2017	1131	0.68	115.11	100.00	Sub Optimal
7	15/05/2017	1129	0.68	114.66	100.00	Sub Optimal
8	10/05/2017	1129	0.68	116.67	98.26	Sub Optimal
9	17/05/2017	1121	0.68	116.19	100.00	Sub Optimal
10	18/05/2017	1119	0.68	119.16	100.00	Sub Optimal

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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.