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.
Then, Davydenko Roy Nikolay should come only once.
Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar. See picture below.
Press and hold Ctrl + Shift.
Press Enter once.
Release all keys.
How to copy array formula
Copy (Ctrl + c) cell E9
Paste (Ctrl + v) array formula on cell range E9:G11
Explaining formula in cell F8
Step 1 - Search for criteria
The SEARCH function allows you to find a string in a cell and it's position. It also allows you to search for multiple strings in multiple cells if you arrange values in a way that works. That is why I use the TRANSPOSE function to transpose the values.