Help with table lookup using array functions

I was hoping someone could take a look at the attached workbook and let me know what I'm doing wrong.

I'm trying to do a lookup on a table where a row is matched by doing a logical test on each column. The row I want is the one that tests true for all columns.

I'm using the match function and array functions to do the test.

Thanks for any help you can provide.

Dave

There should be an easier way but this does the trick
=SUMPRODUCT((A2:A33=J5)*(B2:B33="x")*(C2:C33<J7)*(D2:D33>J7)*(E2:E33<J8)*(F2:F33>J8)*G2:G33)
be aware it will sum all values in G2:G33 where conditions are met

Thanks, that looks like it works.

I can add in checks to make sure I'm matching exactly one row by just summing the tests without the values.

e.g.

=SUMPRODUCT((A2:A33=J5)*(B2:B33=J6)*(C2:C33<J7)*(D2:D33>J7)*(E2:E33<J8)*(F2:F33>J8))


Just for educational purposes ... was my attempt just a completely inappropriate use of a cntrl-shift-enter formula, or is there a change to it that would make it work using the index and match functions?

Thanks!

Dave

Thank you all. This is great.

Problem solved and I learned about a function that I've never tried before (DGET).

Very cool.