RU asks:

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.

Answer:

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

Read this post: Lookup with multiple criteria and display multiple search results using excel formula, part 3

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

Download excel example file

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

Functions in this article:

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

 

 

Related posts:

Lookup with multiple criteria and display multiple search results using excel formula, part 3

Lookup with multiple criteria and display multiple search results using excel formula, part 4

Lookup with multiple criteria and display multiple search results using excel formula

Lookup with multiple criteria and display multiple search results using excel formula, part 2

Search and display all cells that contain all search strings in excel