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.


Array formula in cell E9:

=IFERROR(INDEX($B$3:$D$17, SMALL(IF(MMULT(--(ISNUMBER(SEARCH($F$3:$H$3, $B$3:$D$17))), {1;1;1})=COLUMNS(F3:H3), MATCH(ROW($B$3:$D$17), ROW($B$3:$D$17)), ""),ROWS($A$1:A1)), COLUMNS($A$1:A1)), "")

Search for a text string and return multiple adjacent values

Table of Contents Search for a text string in a column and return multiple adjacent values Search for a text […]

Comments(46) Filed in category: Excel, Search/Find

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.

Learn the basics of Excel arrays

Array formulas allows you to do advanced calculations not possible with regular formulas.

Comments(2) Filed in category: Built-in features, Count values, Excel

How to copy array formula

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

Explaining array formula in cell E9

Read this post:

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

Question: Can expand this equation set into more than two colums of data, say if I had a first, middle […]

Comments(16) Filed in category: Excel

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

(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

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

Returns the rownumber of a reference

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