Quartile Analysis

Hello all,

I'm working on a spreadsheet that has a set of inputs on the left and a table from a report on the right. I need some help to come up with a formula that looks at the various inputs (year, performance metric, strategy, region) and compares that with the appropriate metrics from the report on the right and calculates what quartile the input falls in to. For example, Product A was produced in North America, followed strategy A, and was made during the year 1998. It exhibited a 15.1% return (performance metric 1) and returned 2.00x of the capital invested (performance metric 2). I want the formula to compare each of the two performance statistics (individually) against the relevant year, strategy and region in the data table on the right and return which quartile each performance metric falls in to.

The table on the right has the same fields as the input table - namely year, strategy and region, but it also has the data points for the corresponding upper quartile, median, and lower quartile for each of the two types of performance metrics. Some of the rows in the data table don't have an upper quartile / median / lower quartile listed, so they are just labelled as "n/a".

The exact format of the report isn't super important, but the one condition I do have is that the user has to be able to use this for many different products at the same time. In other words the input table can't be restricted to a single product at a time.

Hopefully that makes a little bit of sense. Please see the attached file. Let me know if you have any questions, and thank you in advance for your help!