I want to combine columns [country code] and [admin1 code] with a "." in between and then lookup that value in Sheet1 in the [admin1code] column. When a match is found I want to get the corresponding provinceid, so in the case of the first row in Country sheet it would return 602, and for the second row it would be 601.
[Country sheet]
geonameid name asciiname latitude longitude feature class feature code country code cc2 admin1 code
2609911 Yttrup Yttrup 56.72314 8.99703 P PPL DK 18
2609915 Yppenbjerg Yppenbjerg 55.61708 11.1826 P PPL DK 20
[Sheet1]
provinceid countryid admin1code
600 15 AT.09
601 15 AT.20
602 15 AT.18
Here's my formula, but I'm getting a #VALUE
error;
=INDEX(Sheet1!A2:C140;MATCH(H2+"."+J2;Sheet1!C2:C140;0);1)
What am I missing?