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: Binning

Approximate match equivalent of VLOOKUP: Binning - 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: Binning

“

Power Query gives us six joins in the interface and they are helpful when we have exact matches. And that's similar to what we get from XLOOKUP and VLOOKUP. However, XLOOKUP and VLOOKUP have components that will allow us to do approximate matches or assign tiers or categories to data. That's not so easily done in Power Query. So I am going to show you one way that we can get things into tiers. For example, this dataset, we've got all of these tests and then we have a contaminant count. So Test 1 had 29 contaminants found. And then we look over on the right side and we see that the 29 falls between 25 and 75, which would be a pass. Test 7, 152 contaminants found. That's in the danger zone. Let's do this with XLOOKUP. You're getting a bonus here, folks. Right. Equals XLOOKUP, double-click. Look up the 29, comma, look for it in this column, comma, return the result, comma, if not found. I'm not worried about that, comma, match mode. We want exact match our next smaller items. So that's…

Contents