Result from multiple criteria and range between two values

[rythmic_flow]

Please can I get help retrieving the Code from Table 2 into Table 1 by searching the "Resource Cost" value.

There are certain criteria/conditions:

1. Group Code must be the same
2. Location code must be the same
3. Resource cost value must fall within the Min and Max Banding.

For example, Table 1 cell A3 (£38.56) should fit the criteria of Table 2 Row 3, as the Group Code and Location are matching, and the value is between £1 and £64.05. The result would be 101.

This is a simplified table, but we have a proprietary coding system that matches several thousand different product costs via a unique code. Sometimes we receive external data and we have to get these matched up and only get the Group Code to start with. I then need to match them by cost banding and obtain the full Code.

Each group will always have five codes, from 1-5. For example, Group 150 will consist of the codes 151, 152, 153, 154, 155, with each one having a min and max cost.

Thanks in advance for the help!

 

[Special-K99]

Does this work - untested (been a while since I did Excel)?
in A3
=SUMPRODUCT((J$3:J$1000=C3)*(M$3:M$1000=B3)*(O$3:O$1000>=A3)*(P$3:P$1000<=A3)*(L$3:L$1000))
and copy down column A

 

[rythmic_flow]

It's returning 0 for me.

I've updated the screen shot below with the column letters.

This is how I arranged the formula:

=SUMPRODUCT((G$3:G$1000=C3)*(I$3:I$1000=B3)*(K$3:K$1000>=A3)*(L$3:L$1000<=A3)*(H$3:H$1000))

 

[rythmic_flow]

Oops, looks like I had the <> the wrong way round. Looks to be working now! Thank you Special-K!