What I need is a code ( or most likely a formula) that will match [A2:B2] to columns M:N and return the offset value in column O in D2
In my example the value in cell D2 should be HIT
Thank you
Printable View
What I need is a code ( or most likely a formula) that will match [A2:B2] to columns M:N and return the offset value in column O in D2
In my example the value in cell D2 should be HIT
Thank you
I have no doubt there is a way to do this without a UDF, but here is a solution for you. Copy and paste the following code into your Module2, and type "=MATCHBOTH(A2:B2,M3:N21)" into D2.[vba]Option Explicit
Function MATCHBOTH(WhatToLookFor As Range, WhereToLook As Range) As String
Dim c As Range
Dim firstAddress As String
Set c = WhereToLook.Columns(1).Find(WhatToLookFor.Columns(1))
firstAddress = c.Address
Do
If c.Offset(, 1) <> WhatToLookFor.Columns(2) Then
Set c = WhereToLook.Columns(1).FindNext
Else
MATCHBOTH = c.Offset(, 2): Exit Do
End If
Loop Until c.Address = firstAddress
End Function
[/vba]
Thank you nst1107 just what's needed