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

Unlock the full course today

Join today to access over 22,600 courses taught by industry experts or purchase this course individually.

Approximate match equivalent of VLOOKUP: Binning

Approximate match equivalent of VLOOKUP: Binning

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

Start my 1-month free trial

Approximate match equivalent of VLOOKUP: Binning

“

- [Instructor] Here we've got a list of test results and we need to match the status with the result in the table in columns B and C. Test one resulted in a 29. According to our scale that's a pass because it's larger than 25, smaller than 75. This would be easy to do in native Excel with XLOOKUP or VLOOKUP. But what if we are stuck having to do this in Power Query? It gets a little tricky and I'm going to show you one technique that's informally called binning. With the cursor in the dataset, From Table/Range. Here's our data. We're going to Add Column, but then I'm going to select this column because that's what we want to reference. Columns From Example, From Selection. Let's slide this over. 29 is a pass. So I'm going to feed this some examples and it's going to start creating a formula for us. 80 is a fail. 72 is a pass. 152, that's danger. Let's give it an excellent, that's the six. We need to give it…

Contents