## Search each column for a string each and return multiple records – OR logic

*Article last updated on February 17, 2018*

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

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

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

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

### How to copy array formula

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

### Download Excel *.xlsx file

multiple-criteria-lookup-with-multiple-unique-results-OR-LOGIC.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(***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

Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]

Filter records that contain numeric values

Liam asks: Hello Oscar, What code is needed to cause cells in Columns F - I to fill with the […]

Here comes another post about the MMULT function, today I made a dynamic scoreboard. There are five women competing and there […]

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 […]

Lookup with multiple criteria and return multiple search results

Question: How do I search a list containing First name column and a last name column? I want to search […]

Search for multiple text strings in multiple cells in excel, part 2

Jerome asks, in this blog post Search for multiple text strings in multiple cells in excel : If the list […]

5 easy ways to extract unique distinct values

You have quite a few options to choose from if you are looking for a way to create a unique […]

Extract a unique distinct list from two columns

Question: I have two ranges or lists (List1 and List2) from where I would like to extract an unique distinct […]

Create a unique distinct alphabetically sorted list

The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]

### 5 Responses to “Search each column for a string each and return multiple records – OR logic”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

Hi Oscar,

Thanks for creating such a helpful website and I've a question if I would like to return the value with a prefix order would it possible? If not can I just add another column in the data and used it as part of the search criteria?

Pat,

Thank you!

Can you describe your problem in greater detail?

Hi Oscar,

Thanks you very much for taking your time to reply, apologies for not explain in much details in my previous post. Basically, I’m working on a scrap form whereby this scrap form is tie to a BOM (as shown per : https://s8.postimg.org/6fjw91ef9/Pat_Data_Table.png ). The scrap form have 2 section;

1. Sub-Assembly

2. Raw Components

When user select the Finishing Good part no from the drop down box, the Sub-assembly and Raw components will be display (show as per https://s15.postimg.org/zfsuxvprv/Pat.png) but this result display was not in the sort order list that I required. I need the return result in the sorting order this is because user will used this data to transact into another system.

P/S : The BOM list (the sorting order) is based on system download.

Once again really appreciate your help.

Best Regards,

Pat

Pat,

Can you provide a workbook?

Pat,

read this post:

Lookup and return multiple values sorted in a custom order