Return value from array in table based on cell values in separate table

Hello!

So, all I'm looking to do is return a value in a table based on a couple values in a separate table. The only thing throwing me for a loop is that one of the values that is to be used to find the matching row in the array table needs to fall between a numeric range defined by cells within the array.

The Primary table, tblTestResults, has the columns [Test] and [Value]. The Reference table, tblTests, has the columns [Test], [Range Low], and [Range High]. The tblTestResults.[Test] should match the array of identical values in tblTests.[Test] and tblTestResults.[Value] should be less than tblTest.[Range High] and greater than or equal to tblTest.[Range Low] ([Range Low] <= [Value] < [Range High]). The expected results were entered manually into the column tblTestResults.[Range Category], but this is where I want the formula to be used.

I appreciate any help!

Cheers!

Try this in D2:

=INDEX(tblTests[Range Category],MATCH(1,(tblTests[Range Low]<=[@Value])*(tblTests[Range High]>=[@Value])*([@Test]=tblTests[Test]),0))

(I think you'll need to enter this as an array formula if you're not on O365. to enter as an array, instead of just hitting Enter, hold the SHIFT and CTRL key while hitting ENTER - you can try it normally at first to see if it works in your version).