Finding the closest 3 values with a criteria

Question

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

Reference to the list of Employees

I wish to write a formula where I could input the Department and Years of Experience to serve as a searching criteria - for example: "HR" and "6" Input Criteria

Search Department:  HR
Years of Experience:    6

The results that I am hoping to get would be the Name, Department, and Years of Experience sorted based on how close the employee's experience is based on the searched department.

For example: I searched "HR" and "6", the result will be ordered: Displaying of Results

Name    Department  Years of Exp
Tom        HR            6
John       HR            5
Sally      HR            8
Tim        HR            8
Simon      HR            9

Answer 1

array formula：

=IFERROR(INDEX(A:A,RIGHT(SMALL(IF($B$2:$B$11="HR",($C$2:$C$11-6)^2/1%%+ROW($2:$11)),ROW(A1)),2)),"")

Answer 2

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

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

Answer 3

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

Answer 4

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)))