Get row for specific value based on two adjacent tables

[YasserKhalil]

Hello everyone

In my sample I have two tables (adjacent) and every table consists of two columns
Need to create UDF to check for value in the first table and the same for a second value in the second table and get the row

For example :
** The value 3.6 is existing between the two values 3.3 and 3.75 (first table : column A:B)

** The value 9.9 is between 9 and 12.6 (second table : column C:D)

These two values 3.6 and 9.9 must be found .. if one of them not found then to return "No Match" in result
The expected result for the example is 10 which is the number of row for the range of tables
I suppose the UDF to have the first parameter the whole range for the two tables (B5:E57) and the second parameter is the range I11 (3.6) and the third parameter is J11 (9.9)

Hope it is clear
Thanks advanced for help

NB: UDF is only required

[karedog]

UDF :

Function Match2(rng As Range, v1, v2)
  Dim arr, i As Long
  arr = rng.Value
  For i = 1 To UBound(arr, 1)
      If arr(i, 1) <= v1 And v1 <= arr(i, 2) And arr(i, 3) <= v2 And v2 <= arr(i, 4) Then Match2 = i: Exit For
  Next i
  If IsEmpty(Match2) Then Match2 = "No Match"
End Function

Formula on cell K11 :

=Match2(B5:E357,I11,J11)

[David A Coop]

Hello Yasser,

Try this

Function GetRow(aCompRng, vComp1, vComp2)
  
    For Each aCell In aCompRng.Columns(1).Cells
        If vComp1 >= aCell And vComp1 <= aCell.Offset(0, 1) Then
           
            If vComp2 >= aCell.Offset(0, 2) And vComp2 <= aCell.Offset(0, 3) Then
                GetRow = aCell.Offset(0, -1)
                Exit Function
            End If
        End If
    Next
    
    
End Function

I think this code does what you want.

You mentioned that you wanted to return the ROW, but in your example you picked up 10 from Column A which is actually on row 14.

If you want 14 to be returned, you can make a slight change to the macro. Change GetRow = aCell.Offset(0, -1) to GetRow = aCell.Row

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[YasserKhalil]

Thank you very much for this wonderful and great solutions
You both are great

I am stuck in making intersect for three tables two tables as illustrated in first post and table across columns
Have a look at the sample workbook .. The expected result is 59 (which is the intersect for the values provided)
I need to create UDF that refers to the two tables as illustrated in the solutions provided then the values to be searched in then the table across the columns then the value to be searched in then then the value provided
I suppose

=myIntersect(B6:E55,Q8,R8,F4:N5,P8)
Hope I don't disturb you

[David A Coop]

You're welcome Yasser,

I see @karedog answered your secondary query yesterday (which I did not see 'ti now), so have not responded.

Thank you for the Rep.

David

[karedog]

Should be :

Function Match3(rng1 As Range, v1a, v1b, rng2, v2)
  Dim arr, i As Long, j As Long
  Match3 = "No match"
  arr = rng1.Value
  For i = 1 To UBound(arr, 1)
      If arr(i, 1) <= v1a And v1a <= arr(i, 2) And arr(i, 3) <= v1b And v1b <= arr(i, 4) Then
         arr = rng2.Value
         For j = 1 To UBound(arr, 2)
             If arr(1, j) <= v2 And v2 <= arr(2, j) Then
                Match3 = arr(i + 2, j)
                Exit Function
             End If
         Next j
      End If
  Next i
End Function

Formula on cell S8 :

=match3(B6:E55,Q8,R8,F4:N55,P8)

[YasserKhalil]

That's very very excellent
Thank you very much my dear friend and my great tutor ..
I will NEVER forget you as far as I breathe
Kind Regards

[karedog]

You are welcome. Thanks for everything.


Regards