PDA

View Full Version : Solved: match and return offset value in cell



ndendrinos
09-03-2009, 09:27 AM
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

nst1107
09-03-2009, 10:11 AM
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.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

ndendrinos
09-03-2009, 12:34 PM
Thank you nst1107 just what's needed