VBA Check if value in Sheet1 exists in Sheet2 then copy specific cells from Sheet2 into Sheet1

[RayfieldComm]

The title here pretty much describes what I need to do. I've done a lot of programming in the past, but not using VBA (or Visual Basic). So this is why I need help.

I what to start at the top of sheet1, in a specific column (address), and work down through sheet1 until it reach the end of this 'address' column.

For each row in sheet 1, it will take the value in cell (a specific address) and search for that value in an 'address' column in sheet2.

If it finds this value in sheet2, then it will go back to sheet1 and look at the value in the 'City' column in the row that was just used for the 'address' value.

Then, a comparison will be done between this value ('City' in sheet1) and the 'City' column in sheet2.

So, I'm wanting to determine if the Address and City in a row in sheet1 can be found in sheet2.

If both Address and City are found in sheet2, then the values for three other cells in this row in sheet2 are copied to specific cells in the 'matching' row in sheet1.

In the table below, BLANK means that there is no value in that cell.

Sheet1:
Name Address City LocationID GeoID Latitude Longitude
Joe Smith 123 Pine St Littletown BLANK BLANK BLANK -BLANK
Tom Tom 47234 S East St. Louis BLANK BLANK BLANK -BLANK
Lisa Smith 5635 South St Chicago BLANK BLANK BLANK -BLANK

Sheet2
LocationID Address City GeoID Latitude Longitude
34842843 123 Pine St Bigtown 3452345645 37.134156 -93.786534
13453893 5635 South St Chicago 3965463322 39.347654 -92.786653
38439032 555 E Washington Smithville 3485629054 36.123456 -94.918236

So, a search is first done in Sheet2, using the Address from Sheet1 (123 Pine St).
This search finds a match in Sheet2.
Then a search is done on the City from Sheet1 on that same row (Littletown)
There is not a match found here.
In this case (no match found for Address and City), I want to place a value of "Not Found" in the LocationID field, for that row, in Sheet1.
The search continues down through Sheet2 until it finds another match on the Address (and then checks the City, as above) or until the end of Sheet2 is reached.

As you can see, there is another match of the Address '5635 South St' found in Sheet2.
When the City is also checked, it also matches in Sheet2.
Now the code should copy the values of LocationID, GeoID, Latitude and Longitude from that row in Sheet2 into the corresponding cells in Sheet1 for that matching row.

And now the search should continue on down the Address (and then City, if an Address match) in Sheet1 until the end of Sheet1 is reached.

 

[skillilea]

How many rows of data do you have?

Have you looked a power query it can do this for you pretty easy.

Can you post your two sheets with XL2BB?

 

[RayfieldComm]

The sheet that I need to search has about 160,000 rows in it.

I have not looked at Power Query.

I'll need to come up with some example sheets of data, as the actual data is proprietary to our company and our customers and under federal law can't be divulged to the general public. But I can get some similar data in a couple of sheets for you.

John

 

[dermie_72]

Index match should provide you with the answer you're looking for. If you use =Index(sheet2 column with city, match(sheet 1 address, sheet 2 Address, 0)) then it will return the city where the address matches on sheet 2. You can change the index column to return different values from the address.
Hope that makes sense.

 

[RayfieldComm]

Can you give me a simple example of using Index Match?

 

[dermie_72]

Assuming Name Address City LocationID GeoID Latitude Longitude are in columns A - G in sheet 1
and LocationID Address City GeoID Latitude Longitude are in columns A-F in sheet 2

=INDEX(Sheet2!C:C,MATCH(Sheet1!B2,Sheet2!B:B,0))

 

[RayfieldComm]

This isn't going to search every row in sheet2 for each row in sheet1. For example, there are over 500 'addresses' in sheet1. For each of these, all 160,000 rows in sheet2 will have to be searched.

Or am I misunderstanding this formula?

 

[dermie_72]

Have you tried the formula?
That is exactly what the formula does. It searches every row in column b sheet 2 for what is in sheets 1 and returns a value for the index row equal to the address.
Exactly as you asked.
Suggest you try the formula and show any results that don’t work

 

[RayfieldComm]

What is "C:C" and "B:B" referring to? Is this a range of cells to use in the search and match?

 

[dermie_72]

Index(sheet2!c:c
Refers to the column that contains the data. The whole column as there’s no numbers.
Match(sheet1!B2
Refers to the value or address you’re looking up. As you drag that down it will change the value it’s looking up.
,sheet2!b:b
Is the range that contains the address you’re looking for.
The ,0 at the end tells the function to look for an exact match.
So it will go down the whole column b in sheet 2 looking for the value in sheet1 b2 (incrementing as you change rows) identify the row that is an exact match and return the value of the same row in the index column.
If you want a different value returned change the index column. If your data is in a different column change the match range. And if the item you’re looking for is in a different cell. Change that.
Now. Have your tried the formula yet?