Two Column Lookup in Excel – Part 1

• 2 mins read

In the image below the table on the left needs to have an allocation column added to it based on the mapping table on the right.

The table on the left is named tblData and the table on the right is named tblMapping.

The combination of entity and account is unique. The account numbers are allocated differently between the two entities.

We can add an allocation column to the table on the left.

Formula solution

The formula for cell E2 is.

=XLOOKUP([@Entity]&[@Account],tblMapping[Entity]&tblMapping[Account],tblMapping[Allocation],”Missing”)

This formula will automatically populate the rest of the column as it is in a formatted table.

The & symbol joins text together.

The first argument of the XLOOKUP joins the entity and the account to form a unique combination.

The second argument joins the columns in the right-hand table together so the combined columns can be looked up.

The third argument is the Allocation column to return if a match is found.

The fourth argument displays the text Missing if a match can’t be found.

Power Query

The next post will demonstrate how to do the same process in Power Query using a two-column merge.