Search each column for a string each and return multiple records

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

How to create an array formula

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.

SEARCH(TRANSPOSE($G$2:$G$4), $B$3:$D$17)




Advanced formulas Concatenate Based on a condition Unique distinct values Drop-down list Join without TEXTJOIN Lookup & return values Compare Criteria, two lists Sums, return unique Extract missing values Common values 3 lists Two price lists Records in two tables Common records Update recent values(2) Missing values two cols Update recent values Values shared by 2 rngs Vals not shared 2 rngs Values shared by 3 rngs Extract not shared vals Shared values 2 cols Missing values 2 cols Combine merge Merge tables Merge rows - condition Combine ranges [UDF] Merge matching rows Merge 2 cols w. blanks Consolidate sheets Combine sheets Merge two columns Merge 3 columns Count values Pattern in cell value Overlapping time Containing any list value Unique distinct/condition Count colored cells Equal values per row Count overlapping days Sort count freq. condi. Count overlap. days(2) Count overlap. days(1) Contiguous values Count students Count filtered table Groups of each value Weekday in date range Weekday date range(2) Cells between value Entries date & time rng Unique distinct - criteria Unique dist. rec. criteria Digits - no duplicates Based on criteria Count groups in calendar Uniq. dist. based dates Count based on date Numbers across sheets Cells between values Most freq/date range Unique distinct months Uniq. dist. 2 cols Unique dist. values Dates Schedule -> date rngs Overlapping time Count overlapping days Date from week & day Count overlap. days(2) Count overlap. days(1) Missing dates Overlap. date ranges Overlapping ranges Find overlap. ranges Min/max date overlap. Date ranges overlap Consecutive dates Count overlap days Prevent overlapping Find date range based on a date Create date ranges Weekday in date range Calc dates in project Weeks from ranges Weekday date range(2) Entries date & time rng Overlap. date ranges Overlap rngs - condition Count groups in calendar Dates from overlap rngs Dates from date rngs Date rngs from dates Dates not in rngs Overlapping ranges Based on date criteria Date, week, month Dupes - date, wk, month Year - month from dates Match date in date rng Latest date - condition Most freq/date range Find min/max date rng Blank between quarters Dupe vals sorted date Create date range Lookup the nearest date Based on a date range Machine utilization Date based on wkday Find numbers in sum Numbers closest to sum No net close to zero Find numbers in sum Numbers in sum Overlapping Overlapping time Count overlapping days Count overlap. days(2) Count overlap. days(1) Missing dates Overlap. date ranges Overlapping ranges Find overlap. ranges Min/max date overlap. Date ranges overlap Count overlap days Prevent overlapping Overlap. date ranges Overlap rngs - condition Overlapping ranges Unique values Case sensitive Unique strings [UDF] Sort digits in rng Based on date criteria From range Sort unique A-Z Min unique number Sort based on date Sum if unique Two columns One column Unique distinct values One column Two columns Three columns Remove blanks Uniq. dist. - condition Adjacent contains string Adjacent has text Contains string Based on criteria Sorted frequency Sorted by sum Sorted alphabetically Sorted A->Z Sorted & no blanks - rng Sort length From a cell range Sorted A-Z from range Contains string in rng Begins with criterion Based on a date range Based on dates Year - month from dates Based on date criteria Extract filtered table Extract & sum condition Two conditions Sum uniq. dist. rec. Assign randomly unique Sums, return unique Sorted, no blanks - rng Duplicate values Dupes A-Z condition Dupes w. exceptions Filter duplicate values using critera Common values 3 lists Label duplicate records Dupes - date, wk, month Dupes from rng contain Filter dupes begins w. Min unique number Dupe vals sorted date Sort dupes A-Z Dupes from range Dupes from 3 cols Dupes from 1 col Dupes from 2 cols Create a list of duplicates where adjacent cell value meets a condition Sort values Sort a column A-Z Sort from 2 cols A-Z Sort rng A-Z Sort column - frequency Frequency - criteria Sort rng - frequency Sort count freq. condi. Sort two columns Sort records Sort table Sort adj. txt horiz. (1) Sort monthly Sort table custom Lookup/Sort custom Sort random order Sort based on length Reverse list Sort dupes A-Z Sort unique A-Z Sort unique A-Z blanks Sort cell delimiter [VBA] Sort Excel table [VBA] Sort based on proximity Sort dates - criteria Vlookup function VLOOKUP function Discount percentages Multiple conditions Lookup values in rng Lookup value in rng Three columns Two datasets Vlookup and return multiple values 5 easy ways Multiple conditions Sorted from A to Z Sorted A-Z - condition Multiple conditions Vlookup range Vlookup rng - records Vlook col - return rng 1 result per search Horizontal vlookup Vlookup - Drop-down Multiple sheets Search and return multiple values Nested Search Multiple conditions Interactive formula Search for string Search records Wildcard vlookup Multiple strings Criteria - AND logic Strings in any 2 cols Strings in 2 cols each Strings in column Contains string - column Filter records Extract using criteria Extract using criteria(2) Extract using criteria(3) Recs betw. this & that Records betw. 2 dates Date range & condition Search text string Filter neg. values Containing digits Records Sort based on freq. Not shared records Sort table custom Records [AND logic] Records in two tables Unique records Common records Common records Duplicate columns Unique distinct records Extract unique distinct records from two data sets Sort based on freq. Filter unique distinct records using criteria Count uniq. dist. [2010] Unique dist. rec. criteria Records - condition(2) Records - condition(1) Filter unique distinct records Count unique distinct records Count unique distinct records Duplicate records Extract duplicate records Count records Weekly Blog EMAIL Get a FREE PDF book! Email Formula categories Count Dates Extract Index and Match Logic Lookups String manipulation Sum Time Vlookup Welcome! I am Oscar and here to help you out. Feel free to comment and ask Excel questions. Make sure you subscribe to my newsletter so you don't miss new blog articles.	Search each column for a string each and return multiple records – OR logic Excel > Search and return multiple values > Search each column for a string each and return multiple records – OR logic Share on Facebook. Share on Twitter. Share on LinkedIn Pin It! Author: Oscar Cronquist Article last updated on December 01, 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. Answer: 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)) How to create an array formula 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. SEARCH(TRANSPOSE($G$2:$G$4), $B$3:$D$17)

get-digital-help.com