Use INDEX and MATCH function to find value and get a corresponding value from the returned rowindex

Question

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?

Answer 1

I believe your error is in the way you combine H2 and J2 (unless it is part of the regional settings like the ;). You need to use the & sign. With index you also just need a one column array and then you can drop the column variable at the end.

=INDEX(Sheet1!A2:A140;MATCH(H2 & "." & J2;Sheet1!C2:C140;0))