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

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

Learn the basics of Excel arrays

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

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 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 […]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 Responses to “Search each column for a string each and return multiple records – OR logic”

### Leave a Reply

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

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

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

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

VBA code

[/vb]

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

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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