POSITION of nth smallest value in multiple criteria

[Sudheer121]

I am getting nth smallest value with multiple criteria but couldnt get the position of it.

I need postition to lookup other values in the same row of the nth smallest

I used the following formulas

=IF(AK$2=0,"",IFERROR(SMALL(IF($K$4:$K$20=AK$2,$I$4:$I$20),$U$4:$U$20),""))

No issue with this formula, but following is not working

=+INDEX($J$4:$J$20,SMALL(IF(AND($I$4:$I$20=AK4,$K$4:$K$20=$AK$2),ROW($I$4:$I$20)-ROW($I$4)+1),COUNTIF($AK$4:AK4,AK4)))

Pls suggest

 

[Fluff]

Hi & welcome to MrExcel.
How about

=SORT(FILTER(FILTER(I4:L20,K4:K20=AK2),COLUMN(I4:L4)<>11))

 

[Sudheer121]

Hi & welcome to MrExcel.
How about

=SORT(FILTER(FILTER(I4:L20,K4:K20=AK2),COLUMN(I4:L4)<>11))

I didnt have SORT function in my Excel

 

[Fluff]

Does it return an error of some sort?
If so what?

 

[Sudheer121]

Does it return an error of some sort?
If so what?

I am not an insider. So Sort function is not available in my system

 

[navic]

Try the following idea if it suits you.

In the 'AK:AM' columns, the result you want is returned but the data is not sorted.
In the 'AP:AR' columns the data are sorted and based on the data from the columns 'AK:AL'

The formulas are as follows:
In the 'AK4' cell ARRAY formula finished with CSE.

=IFERROR(INDEX(I$4:I$17;SMALL(IF(($AK$2=$K$4:$K$17);ROW($I$4:$I$17));ROW(1:1))-3;1);"")

In the 'AL4' cell ARRAY formula finished with CSE.

=IFERROR(INDEX(J$4:J$17;SMALL(IF(($AK$2=$K$4:$K$17);ROW($I$4:$I$17));ROW(1:1))-3;1);"")

In the 'AM4' cell ARRAY formula finished with CSE.

=IFERROR(INDEX(L$4:L$17;SMALL(IF(($AK$2=$K$4:$K$17);ROW($I$4:$I$17));ROW(1:1))-3;1);"")

In the 'AP4' cell

=IFERROR(SMALL($AK$4:$AK$17;ROWS($AK$3:AK3));"")

In the 'AQ4' cell

=IFERROR(INDEX(AL$4:AL$17;AGGREGATE(15;6;(ROW(AL$4:AL$17)-ROW(AL$4)+1)/($AK$4:$AK$17=$AP4);COUNTIF($AP$4:$AP4;$AP4)));"")

In the 'AR4' cell

=IFERROR(INDEX(AM$4:AM$17;AGGREGATE(15;6;(ROW(AM$4:AM$17)-ROW(AM$4)+1)/($AK$4:$AK$17=$AP4);COUNTIF($AP$4:$AP4;$AP4)));"")

 

[Fluff]

I am not an insider.

You do not need to be an insider.

 

[Sudheer121]

Try the following idea if it suits you.

In the 'AK:AM' columns, the result you want is returned but the data is not sorted.
In the 'AP:AR' columns the data are sorted and based on the data from the columns 'AK:AL'

The formulas are as follows:
In the 'AK4' cell ARRAY formula finished with CSE.

=IFERROR(INDEX(I$4:I$17;SMALL(IF(($AK$2=$K$4:$K$17);ROW($I$4:$I$17));ROW(1:1))-3;1);"")

In the 'AL4' cell ARRAY formula finished with CSE.

=IFERROR(INDEX(J$4:J$17;SMALL(IF(($AK$2=$K$4:$K$17);ROW($I$4:$I$17));ROW(1:1))-3;1);"")

In the 'AM4' cell ARRAY formula finished with CSE.

=IFERROR(INDEX(L$4:L$17;SMALL(IF(($AK$2=$K$4:$K$17);ROW($I$4:$I$17));ROW(1:1))-3;1);"")

In the 'AP4' cell

=IFERROR(SMALL($AK$4:$AK$17;ROWS($AK$3:AK3));"")

In the 'AQ4' cell

=IFERROR(INDEX(AL$4:AL$17;AGGREGATE(15;6;(ROW(AL$4:AL$17)-ROW(AL$4)+1)/($AK$4:$AK$17=$AP4);COUNTIF($AP$4:$AP4;$AP4)));"")

In the 'AR4' cell

=IFERROR(INDEX(AM$4:AM$17;AGGREGATE(15;6;(ROW(AM$4:AM$17)-ROW(AM$4)+1)/($AK$4:$AK$17=$AP4);COUNTIF($AP$4:$AP4;$AP4)));"")

Hi navic,

Thanks for the effort you put on for me.

I used the attached formula and its working fine. Thank you.