Hi Guys,
I'm needing to match a product code to a price closest matching the value threshold (we chose 20th percentile within the range). I managed to make a formula that seems to correctly identify closest match, however I'm having issues with automatically defining the range. I was able to define the range for the percentile formula in column D, maybe by stroke of luck (basically I need it to only select a range where the Generic ID is the same. Like, select from range within B2:B4147 where it is equal to B value in the same row as formula).
I tried making something like the below to define the range, however it's just not panning out.
=INDEX(A2:A46100,MATCH(MIN(ABS(IF($B$2:$B$41471=B2,$C$2:$C$46098-D2))),ABS($C$2:$C$46098-D2)),0))
I could manually define the range like I did in the example E column, however I have over 40000 rows to work with and isn't realistic to do manually. I need it to return a matching product that closely matches the price in the 22th percentile, but it needs to be in the same Generic class (column E). If there's a simpler way to determine this, I'm all ears.
Should return a TNDC, where the ProperContractPrice closely matches the value in the 22th Percentile Price, from a range where the Generic ID is the same.

I'm needing to match a product code to a price closest matching the value threshold (we chose 20th percentile within the range). I managed to make a formula that seems to correctly identify closest match, however I'm having issues with automatically defining the range. I was able to define the range for the percentile formula in column D, maybe by stroke of luck (basically I need it to only select a range where the Generic ID is the same. Like, select from range within B2:B4147 where it is equal to B value in the same row as formula).
I tried making something like the below to define the range, however it's just not panning out.
=INDEX(A2:A46100,MATCH(MIN(ABS(IF($B$2:$B$41471=B2,$C$2:$C$46098-D2))),ABS($C$2:$C$46098-D2)),0))
I could manually define the range like I did in the example E column, however I have over 40000 rows to work with and isn't realistic to do manually. I need it to return a matching product that closely matches the price in the 22th percentile, but it needs to be in the same Generic class (column E). If there's a simpler way to determine this, I'm all ears.
Should return a TNDC, where the ProperContractPrice closely matches the value in the 22th Percentile Price, from a range where the Generic ID is the same.
