How do I find the median of a selected column of values within a worksheet

Hey Everyone,

I have been having enormous issues trying to resolve a conditional formatting problem within an Excel Sheet because the cells referenced in the rules I create are dependent on the VLOOKUP function.

I am running Excel on Microsoft Office Professional Plus 2010. I am attaching a "dummy" file that I put together so anyone could play around with the file to get a better idea of what I am trying to do.

.

.

Tab: IP

- Applicable field is the "SELECT PART#" as this is used in my VLOOKUP function

Tab: INSP

- Row 10 is of interest here, specifically the columns of K and L.

- E, F, G will be auto-populated based on the selected part number from the IP tab

- K and L are where the conditional Formatting lies. See "KEY" tab for more on what the three rules are for the conditional formatting.

Tab: DATA TABLE
- Values from this table are the ones being pulled using the VLOOKUP function. Specifically, columns 7 and 8 are important because these columns are referenced directly in the conditional formatting.
Tab: KEY

- This tab highlights the generic form of the VLOOKUP function I am using. The explanation on the right describes what to change within the formula to adjust for a separate column on the "DATA TABLE" tab (highlighted in blue on said tab).

- Additionally, the Goals of the Conditional Formatting are laid out with their generic forms and reasoning for what each rule should be doing

QUESTION (Please refer to excel file for specifics):

On the INSP tab:

The rules for the conditional Formatting are not working how I would like them to. I have them set up to compare the result within the Min/Max column to the Lower/Upper Limit that is auto-populated based on the Part Number chosen on the IP tab. Basically, if the value inputted into Min/Max column is OUTSIDE of the Lower to Upper Limit range; I need the Min/Max cell to format red. Conversely, if the value is INSIDE this range; I need the cell to format to white (no-fill).

Since the Lower/Upper Limit cells contain a formula (VLOOKUP), I believe this is breaking the rules I create and causing any value inputted in the Min/Max section (whether inside or outside of Limit Range) to constantly format the cell red.

.

.

I have exhausted so many hours into messing around with the conditional formatting and research online through various websites and help forums, I do not know what else to do. Please Help.