Finding the closest 3 values with a criteria-CodePudding

I am quite new to excel so I wish to seek some help in the writing of the excel formula for the following scenario:

Example I have a list of employees where the Name, Department and Years of Experience is stated

Name    Department  Years of Experience
John    HR                  5
Mary    Finance             12
Tom     HR                  6
Dick    Finance             11
Sally   HR                  8
Harry   Finance             10
Tim     HR                  8
Duke    Finance             9
Simon   HR                  9
Dolly   Finance             2

CodePudding user response：

try having the formula using the FILTER function just change the ranges as applied to your worksheet

=FILTER(B22:D32,C22:C32="HR")

CodePudding user response：

This can easily be done, using the autofilter basic feature of Excel, why not use that:

CodePudding user response：

Option for O365:

=LET(sorted,SORTBY(A2:C11,ABS(C2:C11-G2),1),FILTER(sorted,(INDEX(sorted,0,2)=G1)*(INDEX(sorted,0,3)>=G2-3)*(INDEX(sorted,0,3)<=G2 3)))