Author: Oscar Cronquist Article last updated on February 17, 2018

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 F8:

=INDEX($B$3:$D$17, SMALL(IF(MMULT(ISNUMBER(SEARCH(TRANSPOSE($G$2:$G$4), $B$3:$D$17))*1,{1;1;1})>0, 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

This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]

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 enter an array formula

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

How to copy array formula

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


Download Excel *.xlsx file


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