VBA Code to match 1 column to two others and provide corresponding results based on match results?

[CarrieB]

First time poster here! I had this until I got to the second condition, now it looks like I need VBA and I'm not as well versed with that. Ok - Not versed at all, a total googler, LOL. I need to search column F and see if the contents match either column P or column S. If yes for P then enter "text a " in cell, if yes for S then enter "text b" in cell, if false leave cell blank... this goes down the entire column N. The formula I had for matching column F and P worked which was this... =IFERROR(IF(MATCH($F4,$P$3:$P$160,0),"text a",),""). I tried several different things in VBA but they were all broken one way or another and I was not able to fix them. I appreciate any help.

 

[JLGWhiz]

Hi CarrieB, welcome to MrExcel Forum
Your formula indicates that you want to check each cell of column F to determine if there is a match anywhere in column P or column S. Not just on the corresponding row to the cell in column F. That is what this macro will do. The problem is that you did not specify which column you want the "text a" or "text b" in. I chose to use column T and you can change that in the code very easily to the correct column. See notations.

Sub matchF()
Dim sh As Worksheet, lr As Long, c As Range
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, "F").End(xlUp).Row
    With sh
        For Each c In .Range("F2:F" & lr)
            If Application.CountIf(.Range("P:P"), c.Value) > 0 Then
                .Range("T" & c.Row) = "text a"   'Change "T" to appropriate column
            ElseIf Application.CountIf(.Range("S:S"), c.Value) > 0 Then
                .Range("T" & c.Row) = "text b"    'Change "T" to appropriate column
            End If
        Next
    End With
End Sub

 

[CarrieB]

Awesome! It works perfect. I doubt I would've come up with this, I did not come close. I spent hours trying to figure it out but in the end, you don't know what you don't know! Thank you so much.

 

[JLGWhiz]

Awesome! It works perfect. I doubt I would've come up with this, I did not come close. I spent hours trying to figure it out but in the end, you don't know what you don't know! Thank you so much.

You're welcome,
Regards, JLG