From the course: Excel: Power Query (Get & Transform)

Unlock the full course today

Join today to access over 23,100 courses taught by industry experts.

Outer join versus XLOOKUP

Outer join versus XLOOKUP - Microsoft Excel Tutorial

From the course: Excel: Power Query (Get & Transform)

Start my 1-month free trial Buy for my team

Outer join versus XLOOKUP

“

From outward appearances, an outer join seems to be doing what a VLOOKUP or XLOOKUP would do for us. Looking at these two datasets; on the left, we've got our list of residents and their parking lot assignments. Over on the right, we've got a list of the owner's, units, and the number of bedrooms in each unit. What I want to do is start with the residents and the car park data, and bring the other data over. So next to B and car Park Number 2, I want to bring over unit B-2 which has two bedrooms. Yes. We could use a VLOOKUP or XLOOKUP to do that, but let's look at this. Notice, in the owner column, Georgette owns B-1 and C-2. Robin owns C-1 and C-4. VLOOKUP, XLOOKUP can't help us with that. Let's do an outer join. I'm going to go to data, queries and connections. You'll see that I have the parking query already and the units query both loaded as connections only. Get data, combine queries, merge. I want the parking query up top. That's going to be my left side. And then the units…

Contents