Ive a formula,
{=SMALL(IF(Sheet1!$A$2:$A$25000=Sheet2!$A$2,IF(She et1!$I$2:$I$25000<=-0.001,Sheet1!$M$2:$M$25000)),11)}but
the 11th largest number repeats itself more than once. I tried this formula,
=SMALL(IF(FREQUENCY(IF(Sheet1!$A$2:$A$25000=Sheet2 !$A$2,IF(Sheet1!$I$2:$I$25000<=-0.001,Sheet1!$L$2:$L$25000)),(IF(Sheet1!$A$2:$A$25 000=Sheet2!$A$2,IF(Sheet1!$I$2:$I$25000<=-0.001,Sheet1!$L$2:$L$25000)))0),(IF(Sheet1!$A$2:$ A$25000=Sheet2!$A$2,IF(Sheet1!$I$2:$I$25000<=-0.001,Sheet1!$L$2:$L$25000)))),11)but
it returned an #N/A.
I tried a different scenario using the formula
=SMALL(IF(FREQUENCY(Sheet1!$L2:$L25,Sheet1!$L2:$L2 5)0,Sheet1!$L2:$L25),9)
and it worked perfectly (once the range didnt have any blank cells). How do
I rectify the problem, and make it work to exclude the blank cells.