Excel Two Column Lookup – Part 2

• 2 mins read

Link to previous post.

https://www.excelyourself.com.au/blog/two-column-lookup-excel-part-1

Power Query

Power Query has a Merge option that enables multi-column lookups.

Below are the two tables we need to merge.

We need to add an Allocation column to the table on the left based on the combination of Entity and Account. The mapping table on the right has the allocations.

The first step is to add both tables to Power Query as connection only queries.

Follow these steps for each table.

1. Right click cell in the table and choose Get Data from table/range.

2. Click the Close and Load drop down and choose Close & Load To.

3. Choose Only Create Connection and click OK.

This creates two separate Power Queries – see image below.

In the Data ribbon click the Get Data drop down. Click Combine Queries and then click Merge.

In the top drop down choose tblData. In the middle drop down choose tblMapping.

Whilst holding the Ctrl key down click the Entity column header in the top section and then click the Account column header in the top section. Small numbers 1 and 2 will appear.

Repeat the process in the bottom section – see image below.

Click OK.

The Power Query window will open.

Click the down arrow at the top right of the tblMapping column – image below.

Untick Entity and Account and Use original column as prefix and click OK – image below.

Allocation added.

Click Close & Load to create a new table with the Allocation column added.

The only problem is the Date column includes time.

You need to change the column type to Date in the query – done in the image below.

This process takes few steps, but if the data you are importing is in a CSV file this process saves double handling the CSV data.