Match multiple criteria within range of values

Question

In Sheet1 I have several input values x, y and z in columns A, B and C. To simplify let's say I only have two rows with values (as in the picture).

In Sheet2 I have the thresholds; min and max-values for x, y and z, in addition to the corresponding codes.

I want to retrieve, in Sheet1, all codes where the input values fall within the thresholds (matching the values) in Sheet2. The codes do not need to be listed in the same cell if this complicates things; they can be listed in separate columns. I am also open for both formulas and VBA.

I know how to list several results using JOINTEXT if the criteria are exact matches. I also know how to list one result based on several inaccurate matches using INDEX + MATCH + <= >=. I don't know how to combine them.

I looked at these:

EXCEL index match by multiple criteria AND multiple match types?

https://exceljet.net/formula/multiple-matches-in-comma-separated-list

https://exceljet.net/formula/index-and-match-with-multiple-criteria

...and tried this:

=INDEX(F5:L8;SMALL(IF(COUNTIF(F5:F8;"<="&A5)*COUNTIF(G5:G8;">"&A5)*COUNTIF(H5:H8;"<="&B5)*COUNTIF(I5:I8;">"&B5)*COUNTIF(J5:J8;"<="&C5)*COUNTIF(K5:K8;">"&C5);ROW(F5:L8)-MIN(ROW(F5:L8))+1);COLUMN(F4));ROW(F4)+6)

...without any result.

Edit your question to include the image as some of us won't be able to open this image. — Imran Malek
possible duplicate of TEXTJOIN for xl2010/xl2013 with criteria — user4039065
Thanks @Jeeped. I will look through your answer in the TEXTJOIN-post and see if this solves my problem. — Jeff

Jeff Jeff · Accepted Answer · 2018-10-31T12:38:59

I managed to solve it by using Jeeped's impressive code (see comments). However, since I'm using comma (,) as decimals-seperator I needed to include a small adjustmen. I changed "iOptions", "iIgnoreHeaderRows", "i" and "j" from Variant to Double, and "a" from Variant to Long. I also included the following code:

Dim counter As Integer, sizeOfPairs As Integer
Dim test As String
counter = 0
sizeOfPairs = UBound(pairs, 1)

For counter = 0 To sizeOfPairs
    If TypeName(pairs(counter)) = "String" Then
        pairs(counter) = Replace(pairs(counter), ",", ".")
    End If
Next counter

Match multiple criteria within range of values

1 Answers