I have a dataset in Sheet 1 where the data forms a long vertical-scrolling list, i.e.

A            B            C        D
Location     Param        Value    Date
L1           Conductivity 1,250    01/02/2019
L2           Conductivity 850      01/02/2019
L1           Chloride     224      01/02/2019
L3           Conductivity 1,900    01/02/2019
L4           Chloride     450      01/02/2019
L4           Conductivity 1,100    01/02/2019
G1           Conductivity 1,250    01/02/2019
G2           Conductivity 1,150    01/02/2019
G3           Conductivity 1,050    01/02/2019
L1           Conductivity 1,299    30/02/2021
L2           Conductivity 857      30/02/2021

I then have, in a separate sheet, a ‘lookup list’ showing all unique locations with some other database information, i.e.

A            B            C
Location     Loc_group    Matrix
L1           Leachate     Water
L2           Leachate     Water
L3           Leachate     Water
L4           Leachate     Water
G1           Groundwater  Water
G2           Groundwater  Water
G3           Groundwater  Water

In a separate sheet again, I perform some statistics (the variable in Cell D1 of this third sheet is ‘Conductivity’ and the named range ‘Date’ refers to Sheet1!D:D).

I’ve been using the following formula to calculate percentiles based on other variables, such as limiting dates and parameter names, i.e.

=PERCENTILE.INC(IF((Sheet1!B:B=D$1)*(Date>43466)*(Date<45657),Sheet1!C:C),0.95)

The formula above gives the 95th percentile for all conductivity values in the date range 01 January 2019 to 31 December 2024, irrespective of their location group (loc_group).

What I need the formula to do, is the above, but also limiting it to only the values in the first sheet, which match the Loc_group criteria (‘Leachate’) in Column B of the second sheet.

Ideally this will be performed in the formula (but if it’s really convoluted, I’m open to VBA).