1

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.

enter image description here

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.

Community
  • 1
  • 1
Jeff
  • 13
  • 6
  • The image is not opening ! – Imran Malek Oct 18 '18 at 07:55
  • Edit your question to include the image as some of us won't be able to open this image. – Imran Malek Oct 18 '18 at 08:06
  • possible duplicate of [TEXTJOIN for xl2010/xl2013 with criteria](https://stackoverflow.com/questions/50716550/textjoin-for-xl2010-xl2013-with-criteria/50719050?s=1|83.9435#50719050) –  Oct 18 '18 at 08:12
  • Thanks for the information! Should be included now. – Jeff Oct 18 '18 at 08:12
  • Thanks @Jeeped. I will look through your answer in the TEXTJOIN-post and see if this solves my problem. – Jeff Oct 18 '18 at 08:19
  • Look at example 3 and add additional `<=` and `>=` criteria. –  Oct 18 '18 at 08:27
  • Sorry for my late response @Jeeped. First of all I'm impressed by your solution. I have tested it and it works perfectly, but only if the input values are whole numbers (int) and not if they are decimal numbers (float). Most of my input values have decimals (max 13 decimals in this data series). I didn't specify this in my initial question, so my bad. I've tried to look at your code but was unable to see how I could solve this. Do you have any suggestions? I'm using comma as decimal separator if that is relevant. BR, Jeff. – Jeff Oct 20 '18 at 12:24

1 Answers1

0

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
Jeff
  • 13
  • 6