Join Excel tables

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

    usa NY
    usa VT

    NY New York
    NY Buffalo
    VT Burlington

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

    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


    pasted in E2 and filled down.

Participate now!

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