Comparing data between two sheets when found past to sheet1

[Tim Hanson]

Not sure how to proceed comparing and shifting between two sheets also would you use vlook up, a double loop?

Any help is appreciated

sheet1

cola    colb       location
stuff    xxx       city1
stuff    bbb       city1
stuff    ooo       city10
stuff    ooo       city7
stuff    ooo       city2
.
.
.

Sheet2

latitude    longitude      city
lat1         lon1             city1
lat2         lon2             city2
lat3         lon3             city3
lat4         lon4             city4
lat5         lon5             city5
.
.
.

Need
sheet1

cola    colb       location     latitude    longitude
stuff    bbb       city1         lat1           lon1
stuff    ooo      city1          lat1          lon1
stuff    ooo      city10        lat10        lon10 
stuff    ooo      city7          lat7          lon7
stuff    ooo      city2          lat2          lon2
.
.
.

 

[SirJB7]

Hi, Tim Hanson!

In 1st worksheet D2 type this:
=SI.ERROR(INDICE(Hoja2!A:B;COINCIDIR($C2;Hoja2!$C:$C;0);COLUMNA()-3);"") -----> in english: =IFERROR(INDEX(Hoja2!A:B,MATCH($C2,Hoja2!$C:$C,0),COLUMN()-3),"")
Copy across and down as required (to column E and row 6).

Regards!

 

[Luke M]

Assuming Sheet 2 is using cols A:C, and Sheet1 is cols A:C as well, in Sheet1:
=INDEX('Sheet2'!A:A,MATCH($C2,'Sheet2'!$C:$C,0))
Copy down and to the right.

 

[Tim Hanson]

Thank you both, both formulas worked great

 

[Tim Hanson]

I spoke to soon, on live WB I have 5 sheets, sheet1 remains sheet1 and the column to match becomes F but sheet2 becomes sheet 5

When I run the code a popup window appears wanting me to upload a file?

Thanks

I have

Sub CopyPastGeolocation()
    Dim LastRow As Long
    Dim ws As Worksheet

    Set ws = Sheets(1)

    LastRow = ws.Range("A" & ws.Rows.count).End(xlUp).Row
   
'************************************************************
    ws.Range("L2:M" & LastRow).Formula = "=INDEX(Sheet5!A:A,MATCH($F2,Sheet5!$C:$C,0))"
   
    Range("L2:M" & LastRow).Copy
    Range("L2:M" & LastRow).PasteSpecial xlPasteValues
'************************************************************

Range("A1").Select
Application.CutCopyMode = False
   
End Sub

 

[Luke M]

Is it really called "Sheet5", or is it "Sheet 5"? If there's any spaces in the worksheet name, the one line needs to be:
ws.Range("L2:M" & LastRow).Formula = "=INDEX('Sheet 5'!A:A,MATCH($F2,'Sheet 5'!$C:$C,0))"

Note the use of single apostrophes.

 

[Tim Hanson]

Luke,

The sheet name is "Geolocation", I thought "Sheet5" was the sheet index then I tired Sheet ("Geolocation") which did not work, then from your comment I tired

=INDEX('Geolocation'!A:A,MATCH($F2,'Geolocation'!$C:$C,0))

And this works, thank you again