On Monday, 25 March 2019 22:49:04 UTC, Milo Bloom wrote:

I have a question, I want to look a 2 different numbers and determine

if a test is pass or fail.

You are given a "curve #" Excel has a list of these curves.

you give excel "unit Weight and Moisture" In the "curves table it

specify a minimum compaction based on the "curve" and moisture array

say Optimum +3% or Optimum +-3% etc.

I posted the sheets here.

'Index of /Excel' (http://pourtney.com/Excel/)

on Line 15 you can see where i was experimenting.

I did make the table on page Curves a named range. Curves

I need columns h & m to meet the specs on page "curves:" column

In other words line 9 column H needs to be 13 to 17% and column n needs

to be greater than 114.2 for a PASS else Fail.

TIA for any Help

Milo

--

Milo Bloom
Hi Milo

Why not create another named range called Curves2 just containing the 4 values you want to use in your Lookup for each curve.

I created a named range called CurvesRow as =Curves!$B$4:$B$10

I then placed the 4 parameters in cells M4:P10, and named this range as Curves2.

Then your formula becomes

=IF(A15="","",LOOKUP(N15,INDEX(Curves2,MATCH(G15,C urvesRow,0),),{"Fail","Pass"}))