EXCEL: How to combine two datasets based on two overlapping columns

Question

How to combine two datasets based on two overlapping columns? see attached small example. Dataset 1 (Date, Depth, NOx) needs to be merged with Dataset 2 (Date, Depth, Si), so that data (NOx and Si) are displayed for matching Date/Depth and left out (empty, null, N/A or similar) when not available.

I imaging this would be done using either VLOOKUP or POWERQUERY.

Answer 1

Hi, thank you for reaching out. I understand how important it is to get your datasets combined properly so your analysis works as expected.

The main reason this issue happens is that Excel’s default lookup functions like VLOOKUP only work with a single column as a key, so when you need to match on both Date and Depth, Excel doesn’t automatically handle that.

To achieve this, you’ll need to use either Power Query or an array-based formula that can check both columns together.

Let’s start with some quick checks: make sure both tables have consistent Date formats and no extra spaces in the Depth column, then convert both into Excel Tables for easier handling.

From there, the easiest method is Power Query, go to Data then Get & Transform then From Table/Range, load both tables, then use Merge Queries by selecting Date and Depth as the matching columns and choose a Left Join so all rows from your first dataset remain.

 After merging, expand the second table to bring in the Si column. If you prefer formulas, you can use an INDEX/MATCH array formula or XLOOKUP to pull Si values based on both Date and Depth.

Can you confirm if you’re using Excel 365 or an older version? That will help me guide you with the exact steps or formula that works best for your setup.