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.

Approximate match equivalent of VLOOKUP: Conditional column

Approximate match equivalent of VLOOKUP: Conditional column - Microsoft Excel Tutorial

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

Start my 1-month free trial Buy for my team

Approximate match equivalent of VLOOKUP: Conditional column

“

The outer joins in Power Query can sometimes look like what we use VLOOKUP or XLOOKUP for. However, VLOOKUP and XLOOKUP give us components for when we don't want to do an exact match but we want to do approximate matches or put our data into categories or tiers. In this example, we have tests and then we have contaminant counts. We want to get these categorized. So the 15 for Test 1, that falls between 10 and 25 so that should get a satisfactory next to it. The 71 in Test 2, that should get a pass because it's between 25 and 75. Let's do this in Power Query by using conditional columns. Here we go. Cursor in the data set. Let's go to data and get data from Table/Range. I want to add a column, conditional column. Call this result. If the contaminant count is less than 10, then excellent. Now, add a clause. We're going to add a number of clauses. All right. So contaminant count is less than 25, then satisfactory. If the contaminant count is less than 75, then pass. If the contaminant…

Contents