Matching Names Between Worksheets

[findjames]

Hi Excel Gurus,

This is the first post of an excel novice, so please bear with me. Here is what I have:


On worksheet X I have two columns, A and B, each column contains list of names.


On worksheet Y, I have column P that contains a list of names.



I want to match the names on column A of worksheet X to the names on column P of worksheet Y, and once the names are matched, have excel give me the names on column B of worksheet X in a different column, say column B of worksheet Y.

Thank you so much for your help!


FJ

 

[L. Howard]

Hi findjames,

So are you saying if the name in X column A does NOT have a match in Y column P it should go in Y column B?

Regards,
Howard

 

[findjames]

Hi Howard,

Allow me to clarify:

In sheet X we have column A, containing version 1 of list of names. In calumn B we have version 2 of list of names (ex. cell A1: Leo Dicaprio; cell B1: Leonardo Dicaprio)

In sheet Y column P we have a list of names that contains many names that correspond to the names in sheet X column A


Im' hoping to perform this: if the names in sheet Y column P matches names in sheet X column A, then give me the the version of names under sheet X column B

I'm hoping to project the result in column B of sheet Y



I'm grateful for your help. I hope my explanation doesn't confuse you. Thank you!

FJ

 

[Gavin T]

Hi findjames,

If I understand you correctly you may want to use a VLOOKUP formula.

 

[L. Howard]

Let's see if I understood your clarification.

Copy and paste this in Sheet1 module.
Where you see "Sheet1" and "Sheet2" you will need to enter your real sheet names there.
Here Sheet1 is = sheet X and Sheet2 is = to sheet Y in you explanation.

Howard

Option Explicit

Sub MatchAPB()
Dim c As Range
Dim p As Range
Dim aRow As Long
Dim pRow As Long
Dim aRng As Range
Dim pRng As Range

aRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Set aRng = Sheets("Sheet1").Range("A1:A" & aRow)

 pRow = Sheets("Sheet2").Cells(Rows.Count, "P").End(xlUp).Row
Set pRng = Sheets("Sheet2").Range("P1:P" & pRow)

For Each c In aRng
  For Each p In pRng
    If c = p Then
      c.Offset(, 1).Copy Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp)(2)
    End If
  Next 'P
Next 'c

End Sub