Look up Location from another sheet

[nt_beans]

hi, i have a workbook with two sheets (sheet1 and sheet2).
Sheet 1 has the First Name, Last Name and Location of each user.

First Name	Last Name	Location
Paul	Allen	New York
Whitney	Campbell	Chicago
Alan	Baretto	Los Angeles

<tbody>
</tbody>

Sheet 2 only got First Name and Last Name. There can be multiple names of same user.
I need to populate location for each user

First Name	Last Name	Location
Alan	Baretto
Alan	Baretto
Whitney	Campbell
Alan	Baretto
Whitney	Campbell

<tbody>
</tbody>

Please let me know the best way to populate location for each user in sheet2.

thanks
NT

 

[oldbrewer]

						concatname		row 1
Paul	Allen	New York				PaulAllen
Whitney	Campbell	Chicago				WhitneyCampbell
Alan	Baretto	Los Angeles				AlanBaretto
Alan	Baretto	Los Angeles	#####			AlanBaretto
Alan	Baretto	Los Angeles				AlanBaretto
Whitney	Campbell	Chicago				WhitneyCampbell
Alan	Baretto	Los Angeles				AlanBaretto
Whitney	Campbell	Chicago				WhitneyCampbell
			#####
			=OFFSET($G$1,MATCH(G15,$G$2:$G$4,0),-4)
	the concat names are in helper column G

<colgroup><col><col><col><col span="3"><col><col span="4"></colgroup><tbody>
</tbody>

 

[nt_beans]

=OFFSET($G$1,MATCH(G15,$G$2:$G$4,0),-4) brings "0"

any idea why G15.

 

[nt_beans]

any help will be greatly appreciated

 

[Fluff]

If your interested in a macro, try

Sub Getlocation()

   Dim Cl As Range
   Dim ValU As String
   Dim Ws1 As Worksheet
   Dim Ws2 As Worksheet
   
   Set Ws1 = Sheets("sheet1")
   Set Ws2 = Sheets("sheet2")
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws1.Range("A2", Ws1.Range("A" & Rows.Count).End(xlUp))
         ValU = Cl.Value & Cl.Offset(, 1).Value
         If Not .exists(ValU) Then .Add ValU, Cl.Offset(, 2).Value
      Next Cl
      For Each Cl In Ws2.Range("A2", Ws2.Range("A" & Rows.Count).End(xlUp))
         ValU = Cl.Value & Cl.Offset(, 1).Value
         If .exists(ValU) Then Cl.Offset(, 2).Value = .Item(ValU)
      Next Cl
   End With
End Sub

 

[nt_beans]

If your interested in a macro, try

Sub Getlocation()

   Dim Cl As Range
   Dim ValU As String
   Dim Ws1 As Worksheet
   Dim Ws2 As Worksheet
   
   Set Ws1 = Sheets("sheet1")
   Set Ws2 = Sheets("sheet2")
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws1.Range("A2", Ws1.Range("A" & Rows.Count).End(xlUp))
         ValU = Cl.Value & Cl.Offset(, 1).Value
         If Not .exists(ValU) Then .Add ValU, Cl.Offset(, 2).Value
      Next Cl
      For Each Cl In Ws2.Range("A2", Ws2.Range("A" & Rows.Count).End(xlUp))
         ValU = Cl.Value & Cl.Offset(, 1).Value
         If .exists(ValU) Then Cl.Offset(, 2).Value = .Item(ValU)
      Next Cl
   End With
End Sub

thanks again

 

[Fluff]

You're welcome

 

[oldbrewer]

						concatname		row 1
Paul	Allen	New York				PaulAllen	< < G2
Whitney	Campbell	Chicago				WhitneyCampbell
Alan	Baretto	Los Angeles				AlanBaretto	< < G4
		col C
Alan	Baretto	Los Angeles	#####			AlanBaretto	< < G15
Alan	Baretto	Los Angeles				AlanBaretto
Whitney	Campbell	Chicago				WhitneyCampbell
Alan	Baretto	Los Angeles				AlanBaretto
Whitney	Campbell	Chicago				WhitneyCampbell
			#####
			=OFFSET($G$1,MATCH(G15,$G$2:$G$4,0),-4)
	the concat names are in helper column G
			the minus 4 at the end sends you from col G to col C
			where the locations are

<colgroup><col><col><col><col span="3"><col><col span="4"></colgroup><tbody>
</tbody>