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

CarrieB

New Member
Joined
Sep 25, 2014
Messages
3
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.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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.
Code:
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
 
Last edited:

CarrieB

New Member
Joined
Sep 25, 2014
Messages
3
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,016
Messages
5,834,943
Members
430,327
Latest member
Mr_Himalayan778

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top