The group is set in cell G4, and the target value is entered in cell G5.
The allowed tolerance is entered in cell G6.
The data comes from anExcel Tablecalleddatain the range B5:D16.
The solution is built on theFILTER functionwhich can be used to extract and list data that meets multiple criteria.
The beauty of this formula is that tolerance calculations do not need to be in the source data.
The FILTER function creates the data it needs on the fly.
Background reading
This article assumes you are familiar with Excel Tables and the FILTER function.
Combining expressions
Now we need to combine both logical conditions above into a single formula.
For this, we useBoolean logic.
Because we want to join the two expressions with AND (i.e.
The standalone formula above will return 1 for values that meet both conditions, and 0 for other values.
These results are returned to cell F9 andspillonto the worksheet.
If any of the variable inputs in G4:G6 are changed, results are immediately updated.
The output from FILTER is dynamic.
If source data or criteria change, FILTER will return a new set of results.
ABS converts negative numbers to positive numbers, and positive numbers are unaffected.
Related videos
FILTER function basic example
