How to VLOOKUP From Another Excel Sheet In Excel Power Query ? ( Merge Queries )

In this article I am going to tell you how to VLOOKUP in Excel Power Query ? If you have not heard about power query , don’t worry. In this article you will take a look at -

Import data from another excel sheet.
Import data in excel power query .
Transform data in Excel power query .

So lets start ,
We have two excel sheets, one is the Sales Report and the second one is the Region Map.

There are two columns in the Region Map - State and Region. We have a State column in the Sales Report .

So we want to lookup region of the state from the region map sheet .

Creating and Opening New Excel Sheet

To create a new worksheet right click on mouse and go to New > Microsoft Excel Worksheet. Give it a name whatever you want . Now open the Excel Sheet .

Importing Another Workbook In Microsoft Power Query

After opening the newly created excel sheet now we are going to import our both excel sheets in this excel file or Power Query.

To Import these excel files in power query follow these steps -:

First of all click on the DATA tab from the top of the excel sheet.
Now click on New Query > From File > From Workbook . This will open file explorer to select the excel workbook for import.

Now we have two files to import . We have to select a single file at once. We select the Sales Report and click on Import.

Now a Navigator tab will open . In this tab first we have to select the sheet name and after that click on Transform Data .

After completing previous step our data will open in Microsoft Power Query Editor. Now we have to import the Region Map sheet within the Power Query.
To import the region map sheet click on New Source > File > Excel , within the Home tab .

Now select the sheet and click on import. Now in navigator tab click on sheet name and click on OK.

Now both sheets have opened in Microsoft Power Query Editor. We can view both sheets in the left pane named Queries within the Power Query Editor.

VLOOKUP In Power Query Editor ( Merge Queries )

Now we VLOOKUP which state falls in which region ? In Power Query this function is called Merge Queries.

Follow these steps to VLOOKUP the region of state from region map sheet -:

Very first select the sheet which has the vlookup value . Here I want to vlookup state name in the orders data sheet .So I have selected the Orders Data Sheet.
Now click on Merge Queries within the Home tab.

A merge tab will open . Select the column which one you want to VLOOKUP.Now select the Region Map sheet from here we want to vlookup . Now select the State column (matching column) from the region map .
In the Join Kind dropdown select Left Outer (all from first , matching from second).
If there is no error you see a green right symbol and select the OK.

Now a region map expandable column added in Order Data Sheet.
Click On expand icon from top right of region map column.

Now a filter UI will open , select the region field and click on OK.

Now a Region Map. Region Column added to our sheet with vlookup region . You can simply move your column by holding and dragging it.
Now click on Close and Load from top left . Now our data is loaded in Excel Sheets with a region map column.

In future if you have added more data or edited your sales report . Then you don't need to do these steps again . Simply refresh the data in Excel Sheet and you data have been updated with new data.