It is common to perform a lookup based on one column. Performing a lookup based on two columns is more unusual. This is a formula technique you can use to perform a two-column look up.
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.
Hello Neale,
Thank you for this tip that I did not know about. I’ve always added an extra column with an ID up to now to manage this (both in Excel and Power Query).
I’ve tested straight away and noticed it is however slow with bigger datasets.
Regards,
Pascal, Switzerland
Hi Pascal
Power Query can also lookup based on two columns – that is Part 2 – link below. It might perform better on huge data sets.
https://a4accounting.com.au/excel-two-column-lookup-part-2/
I don’t test my solutions on huge data sets as 99% of Excel users don’t use huge data sets. Plus if the solution works, waiting a little while is acceptable.
Regards
Neale