Location Via City and Country

[blackbranded]

I am trying to get the Location from Sheet 1 to Result(44) sheet. problem is if in Australia have city which in USA as well so location will add in both which is wrong
so if any one help me out on this to get the location VLOOKUP with city and country would be great

here is the Google sheet

Location Sheet

result (44) Domain,COUNTRY,City,Citylower,Location cvtaus,Australia,Canberra,canberra cvtaus,Australia,Camberwell,camberwell cvtaus,Australia,Carnegie,carnegie cvtaus,Australia,KearneysSpring,kearneysspring cvtaus,Australia,Malvern,malvern cvtnzl,NewZealand,SpeargrassFlat,speargrassflat cvtnzl,N...

docs.google.com

 

[GraH - Guido]

One can concatenate the fields and then return a value from a range through index/match.
INDEX ( Locations, MATCH ( Country&City , Countries&Cities, 0 ) ).

Replace locations, city, country, cities and countries with the applicable ranges.

How To Use INDEX & MATCH Functions With Concatenated Values In Excel

In this tutorial we are going to look at a typical INDEX / MATCH combination formula but with a twist that concatenates both the lookup values and the arrays that are being looked into, making it an array formula.

excel-bytes.com

.