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.

 

[RayfieldComm]

No, I haven't tried the formula yet. I was trying to better understand the syntax of this formula. If this was in a syntax of a programming language that I'm familiar with, it would be a lot easier for me to understand.

Shouldn't Sheet2!C:C be Sheet2!B:B, because column B in sheet two is where the data is for which I'm searching for a Match with Sheet1!B2?

 

[dermie_72]

the formula is correct based on the data you provided. Please try the formula and confirm if its not working. Until then, this might help explain index match

Let Me Google That

For all those people that find it more convenient to bother you with their question than to google it for themselves.

letmegooglethat.com

 

[RayfieldComm]

I did have to change the Sheet1 cell from B2 to S2, as the addresses for which I'm searching in Sheet2 are in column S.

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

This gave an error, that a value wasn't available for this formula.

 

[dermie_72]

please put your data into an XL2BB file so I can see the layout