excel - Find match and copy data -

i have following (sheet1):

state           region south australia aberfoyle park new south wales abermain south australia adelaide 

and (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 efficient way search through sheet1 , match region , state in sheet2, when match found create 2 new columns (in sheet1) copies matched long , lat sheet2, final output should (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 lot of more data shown here simple copy past not work :)

thanks in advance.

in sheet1!c2 gather correct data two-column-matching records in several ways. here 3 scenarios , examples.

1. use two-column criteria on lookup based on index function. there array formulas prefer standard formulas not require cse. these pick first occurrence of matched pair of state/region minor rewrite can made return multiple matches multiple rows. example 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 simple sumifs function. work best when there 1 set of matching state/region return. multiple matching pairs produce incorrect, aggregated results. example c2:
   =sumifs(sheet2!c:c, sheet2!$a:$a, $a2, sheet2!$b:$b, $b2)
3. if there multiple matched pairs , want mean value returned average of geographical points, use averageifs function. work best when there multiple location points never return 1 point unless there 1 set of matching state/region return. example c2:
   =averageifs(sheet2!c:c, sheet2!$a:$a, $a2, sheet2!$b:$b, $b2)

after putting of examples sheet1!c2 latitude fill right 1 column pick longitude , fill c2:d2 down pick of matches.