How to index and match geographical areas broken in four columns

[alliage]

Hi, I am John Alliage from GMA News, a broadcast network based in the Philippines.

We’re doing a story about the conditional cash transfer in our country. I hope you could help me with my Excel query:

I have two excel files, named BHS_REGIONALIZED and 2011_GEOGCODE_REGIONALIZED.

The GEOGCODE file contains a geographic code, region, province, municipality, and barangay, whereas my BHS file contains only region, province and barangay.

I would like to include the municipality record in the BHS file which doesn’t contain such information. I would like that all region, province and barangay columns match between the BHS_REGIONALIZED and GEOGCODE_REGIONALIZED files. One peculiar fact in our country’s geopolitical composition is that barangays from different municipalities and/or provinces can have the same name. For instance, the “San Roque” name can be found in at least a hundred barangays spread across municipalities and provinces.

So I would like that all regions, provinces and barangays should match, so that the REAL municipality will be the result in the end.

From here, it’s easy for me to include the geographical code for easy database construction.

This is the excel construction for BHS_REGIONALIZED:
A B C D

Region Province Municipality Barangay

1 CARAGA AGUSAN SAN JOSE
2 CARAGA ISABELA SAN JOSE



This is the excel construction for the GEOGCODE_REGIONALIZED
A B C D

Region Province Municipality Barangay

1 CARAGA AGUSAN MISAMIS SAN JOSE
2 CARAGA ISABELA CAGAYAN SAN JOSE

 

[Andrew Poulsom]

Sample formula:

=INDEX(Sheet2!C$1:C$3,MATCH(1,INDEX((Sheet2!A$1:A$3=A2)*(Sheet2!B$1:B$3=B2)*(Sheet2!D$1:D$3=D2),),FALSE))

Replace the reference to Sheet2 with a reference to the sheet in GEOGCODE_REGIONALIZED.

 

[alliage]

Sample formula:

=INDEX(Sheet2!C$1:C$3,MATCH(1,INDEX((Sheet2!A$1:A$3=A2)*(Sheet2!B$1:B$3=B2)*(Sheet2!D$1:D$3=D2),),FALSE))

Replace the reference to Sheet2 with a reference to the sheet in GEOGCODE_REGIONALIZED.

Hi, it said that I've entered too many arguments.

Can you send your e-mail address through private message so I can e-mail you my file?
Here's mine: alliage.morales[at]gmail.com

 

[Andrew Poulsom]

The formula works for me. Did you copy it from the Board and paste it into your worksheet?

 

[alliage]

The formula works for me. Did you copy it from the Board and paste it into your worksheet?

Hi, can I get your email address so I can email you a sample file?
Here's mine: alliage.morales[at]gmail.como

 

[Andrew Poulsom]

Copy the formula from the Board and paste it into your worksheet. Do you still get the error?