Join Excel tables

  • My 2 tables are presented in Excel as in this example:


    TABLE1:
    COUNTRY STATE
    usa NY
    usa VT


    TABLE2:
    STATE CITY
    NY New York
    NY Buffalo
    VT Burlington


    I need to join the 2 excel sheet and result to be as follow


    TABLE3:
    COUNTRY STATE CITY
    usa NY New York
    usa NY Buffalo
    usa VT Burling


    Currently exploring which (1) predefined function or (2) formula could do this.
    I have thousands of CITY equivalent, 100 of STATE equivalent.
    (3) VB would be my last resort.
    Any help is appreciated.

  • Re: Join Excel tables


    If you are using Excel 2010 or later, you may easily achieve this with the help of Power Query. If this is the case, let me know, I will guide you through the required steps.
    You will need to download and install Power Query from MS site, it's a free Add-In.

  • Re: Join Excel tables


    If you reverse your country and state columns in the order STATE COUNTRY at A1 and B1 respectively. Then you could use a vlookup in the second table. In ohter words if your second table was STATE CITY and you added a column before those the be COUNTRY STATE CITY in E1 through G1 respectively then you could use the formula

    Code
    =VLOOKUP(F2,$A$2:$A$3,1)

    pasted in E2 and filled down.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!