The k-th smallest elment of a sub list defined dynamically form a super set based on 2 criteria

[adnane]

Hello,

I'm trying to solve the below problem using only Excel:

-Column A contains dates
-Column B contains scenarios number
-Column C contains object
-Column D contains a risk measure for this particular Object for a given date and scenario

My challenge here is to find the k-th smallest risk measure (column D) for a given Object and Date using only Excel and ideally with only one formula (without using too many helpers functions).

Obviousely, here we need first to find the sub list of possible risk measure based on the given object and date, then we calculate the k-th smallest elements of this sub list.

I was hoping to solve this using some previous threads about subarray formulas in excel (using index, row, etc) but no clue.

Many thanks in advance for your assitance with this matter.

ad.

 

[BiocideJ]

The following array-formula will do what you are wanting.

=SMALL(IF($D$2:$D$500*($C$2:$C$500=$I$3)*($A$2:$A$500=$I$2)<>0,$D$2:$D$500*($C$2:$C$500=$I$3)*($A$2:$A$500=$I$2),""),$I$4)
This formula will need to be confirmed with CTRL+SHIFT+ENTER after entering it in the cell and you will see curly braces {} surrounding the formula that Excel adds if done correctly.

NOTE: I am assuming your values start in row 2 (with row 1 as headers) You can edit the $x$2:$x$500 ranges to however many rows of data you have.
Also, the cells to lookup are defined as
$I$2: Lookup Date
$I$3: Object
$I$4: k