Trying to Incorporate Criteria During Lookup

[fixthis]

Looking for formula that can do the lookup shown in attached table with the added criteria that the lookup value not include products that have 100% VOC content. In other words incorporate criteria (e6:e13<1) at the appropriate place in the formula at J160.

So in my real sheet, I have a drop down at D158 that chooses a chemical. I want the formula in J160 to go to the corresponding column (Triethylene (Col J) in this example) and pick the highest wt% value that does not have a corresponding VOC content of 100% (Col E), then use that value along with the actual VOC content to compute the total pounds. So for this example, it should use 28% wt and 8.41 lb/gal values along with the other parameters located at D159 thru D160.

I realized that in my attempt, the first formula in J158 is not really obtaining the maximum value in the Triethylamine range, just the first. So I think that is where I cannot seem to succeed in getting the highest value not having the corresponding VOC content of 100%.

 

[pecoflyer]

The two first values are straightforward. As I don't understand the formula for the total pounds, I will leave it up to you based on the found values

 

[fixthis]

Hi pecoflyer,

Thank you for your suggested approach. The first formula in J168 using AGGREGATE is very interesting and works spot on. I tried using the evaluate formula tool to understand the function but it kind of skips steps. If you can explain how that is works that would be great.

Your second formula at J169 does not work for other chemical choices as the column within the MATCH is hard coded for the column for triethylamine ($J$6:$J$13). I need it to be flexible based on the chemical choice in D158.

I also probably was not clear on this, but I would like the two formulas to be combined in one as it will be actually going into a different worksheet. Sort of what I attempted in cell J160 with actual answer (for triethylamine) at J166.

 

[pecoflyer]

Your second formula at J169 does not work for other chemical choices as the column within the MATCH is hard coded for the column for triethylamine ($J$6:$J$13)

My bad, see the corrected formula attached
There are other solutions but without knowing your Excel version ( this is a flaw in this forum), I stayed with basics

 

[pecoflyer]

The AGGREGATE function used is for obtaining LARGE values (function number 14) and ignore errors as option (6) with option 1 for returning the largest number
The formula 1/.... returns an array of numbers and errors verifying the given condition (<100%)
The INDEX part returns a reference to an entire column of the range ( therefore using the ,, in the formula) defined here by the column header referring to the Chemical); ( When INDEX is embedded, it returns a reference to a cell or range)
Multiplying the two arrays we now get an array with errors ( DIV#0) and numbers
Option 6 makes the formula ignore errors
As we have chosen the largest number with the 1 option in the AGGREGATE function, the largest number smaller than 100% is the result

 

[fixthis]

Thank you again. That second formula worked fine.
I was able to combine the two formula steps into one formula and it worked.
I appreciate the explanation as well.
Learning about setting the row or column within INDEX to zero or blank causes the function to return an array instead of a single row/column is something I did not know.
By the way I am using Excel 2019 on Windows platform.