Can you please suggest if i want to find out the rows with fixed value in "First Name" but, if either of the criteria for "Middle Name" or "Last Name" will suffice. Also, i don't want repeated values in the final sheet.
For eg:
FN: a
MN: o
LN: o
Then, Davydenko Roy Nikolay should come only once.

### Array formula in cell E9:

=IFERROR(INDEX(\$A\$2:\$C\$16, SMALL(IF((ISERROR(SEARCH(\$F\$4, Last_name)*SEARCH(\$F\$3, Middle_name)*SEARCH(\$F\$2, First_name)))+(COUNTIFS(\$E\$8:E8, Last_name, \$F\$8:F8, Middle_name, \$G\$8:G8, First_name)>0), "", ROW(Last_name)-MIN(ROW(Last_name))+1), 1), COLUMN(A1)), "")

### How to create an array formula

1. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar. See picture below.
2. Press and hold Ctrl + Shift.
3. Press Enter once.
4. Release all keys.

### How to copy array formula

1. Copy (Ctrl + c) cell E9
2. Paste (Ctrl + v) array formula on cell range E9:G11

### How to create named ranges

1. Select cell range A2:A16
2. Type Last_name in name box. See picture above.
3. Press Enter
4. Repeat with remaining ranges

Last_name - A2:A16
Middle_name - B2:B16
First_name - C2:C16

### Array formula in cell H9:

=IFERROR(SMALL(IF((ISERROR(SEARCH(\$F\$4, Last_name)*SEARCH(\$F\$3, Middle_name)*SEARCH(\$F\$2, First_name)))+(COUNTIFS(\$E\$8:E8, Last_name, \$F\$8:F8, Middle_name, \$G\$8:G8, First_name)>0), "", ROW(Last_name)), 1), "")

### How to copy array formula

1. Copy (Ctrl + c) cell H9
2. Paste (Ctrl + v) array formula on cell range H9:H11

### Explaining array formula in cell E9

COUNTIFS(\$E\$8:E8, Last_name, \$F\$8:F8, Middle_name, \$G\$8:G8, First_name)>0 filters unique distinct records.

multiple-criteria-lookup-with-multiple-unique-results.xlsx
(Excel 2007 Workbook *.xlsx)

IF(logical_test, [value_if:true], [value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

ROW(reference)
Returns the rownumber of a reference

SMALL(array,k)
Returns the k-th smallest number in this data set.

SEARCH(find_text,within_text, [start_num])
Returns the number of the character at which a specific character or text string is first found, reading left to right (not case sensitive)

COUNTIFS(criteria_range1,criteria1, criteria_range2, criteria2...)
Counts the number of cells specified by a given set of conditions or criteria