Approximate Match Lookup in Excel and Power Query

Today we are going to discuss about numerous methods in Excel and Power Query to do approximate match lookup.

In our dataset, the Employee table contains the salary information of employees, while the Tax Rate table lists tax rates according to different salary ranges.

Our goal is to retrieve the tax rate for each employee in the Employee table based on their salary. Since the tax rate table specifies ranges, we will need to perform an approximate lookup.

Method-1:

Formula in M4 cell,

=LOOKUP(D4:D13,F4:G8)

This formula is designed for a single cell in Office 365. To use it in Excel 2016 or earlier, refer to cell D4 instead of the range D4:D13, and then drag the formula down.

Method-2:

=VLOOKUP(D4,$F$4:$G$8,2)

By default, VLOOKUP performs an approximate match, so we can either omit the match_type argument or use 1 or TRUE.

Method-3:

=XLOOKUP(D4:D13,F4:F8,G4:G8,,-1)

M-Code:

f -> refers to each row in Employee Table,

x -> refers to reach row in Tax Rate Table.

let

Source = each Excel.CurrentWorkbook(){[Name=_]}[Content],

Result = Table.AddColumn( Source("Table1"), "Tax Rate", (f) => Table.Max(Table.SelectRows(Source("Table2"), (x) => x[Threshold] <= f[Salary]),"Threshold")[Tax Rate], Percentage.Type)

in

Result