CODEWITHSUNDEEP
excelvba
user1470994
user1470994

Reputation: 311

Find match and copy data

I have the following (sheet1):

State           Region
South Australia Aberfoyle Park
New South Wales Abermain
South Australia Adelaide

And then (sheet2):

State           Region          Long       Lat
Victoria        Abermain        -12.8167   51.4333
New South Wales Abermain        -32.8167   151.4333
South Australia Aberfoyle Park  -35.07     138.5885

I require an efficient way to search through sheet1 and match the Region and State in sheet2, when a match is found I would like to create two new columns (in sheet1) that copies the matched Long and Lat from sheet2, the final output should look like (in sheet1):

State           Region          Long       Lat
South Australia Aberfoyle Park  -35.07     138.5885
New South Wales Abermain        -32.8167   151.4333

Note; sheet1 contains a lot of more data that is shown here so a simple copy past will not work :)

Thanks in advance.

Upvotes: 0

Views: 105

Answers (1)

user4039065
user4039065

Reputation:

In Sheet1!C2 you could gather the correct data from two-column-matching records in several ways. Here are three scenarios and examples.

  1. Use a two-column criteria on a lookup based on the INDEX function. There are array formulas that do this but I prefer standard formulas that do not require CSE. These only pick up the first occurrence of a matched pair of state/region but a minor rewrite can be made to return multiple matches into multiple rows. Example for C2:
    =iferror(index(Sheet2!C$1:C$9999, min(index(row($1:$9999)+((Sheet2!$A$1:$A$9999<>$A2)+(Sheet2!$B$1:$B$9999<>$B2))*1e99, , ))), "")
  2. Use a simple SUMIFS function. This would work best when there are only one set of matching State/Region to return. Multiple matching pairs would produce incorrect, aggregated results. Example for C2:
    =sumifs(Sheet2!C:C, Sheet2!$A:$A, $A2, Sheet2!$B:$B, $B2)
  3. If there are multiple matched pairs and you want a mean value returned from the average of the geographical points, use an AVERAGEIFS function. This would work best when there are multiple location points but will never return any one point unless there was only one set of matching State/Region to return. Example for C2:
    =averageifs(Sheet2!C:C, Sheet2!$A:$A, $A2, Sheet2!$B:$B, $B2)

After putting any of those examples into Sheet1!C2 for the latitude fill right one column to pick up the longitude and then fill C2:D2 down to pick up all of the matches.

Upvotes: 1

Related Questions