Please see attached.

Notes:

• In Sheet3, you need to separate the parameters corresponding to Columns B and C of Sheet1... and each cell needs to be populated as shown in my inserted columns B and C of Sheet1.

• The entries in columns B and C of Sheet3 need to match exactly the entries in column B and C of Sheet1.

• If you have a dynamic table in Sheet1, you should convert your table into an Excel Table, or use Dynamic Named Ranges to allow for expansion. Then use those ranges in the formulas in sheet3.

Formula in Sheet3, D2 is:

=IFERROR(LARGE(IF((Sheet1!$A$1:$A$1000=D$1)* (Sheet1!$B$1:$B$1000= $B2)*(Sheet1!$C$1:$C$1000=$C2), Sheet1!$O$1:$O$1000), COUNTIFS($B$2:$B2,$B2,$C$2:$C2,$C2)),"FIX-IT")

confirmed with CTRL+SHIFT+ENTER not just ENTER as it is an Array Formula. You will notice { } brackets appear around the formula.

Then you copy it down and across the table to get your results.

Hope it helps.

Attached Files

Copy of EMPLOREPORT20170823SAMPLE.xlsx (124.3 KB, 14 views)