## Search columns for a string and return records – AND logic

**Question:**

Can expand this equation set into more than two colums of data, say if I had a first, middle and last name column could I only display the values in which all three cases are true?

*This blog article answers a question in this article: Lookup with multiple criteria and display multiple search results using excel formula*

Array formula in F8:

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

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

becomes

SEARCH(TRANSPOSE({"a"; "o"; "o"}), $B$3:$D$17)

becomes

SEARCH(TRANSPOSE({"a"; "o"; "o"}), {"Federer", "Peter", " Roger ";"Djokovic", "Ted", " Novak ";"Murray", "Steven", " Andy ";"Davydenko", "Roy", " Nikolay ";"Roddick", "James", " Andy ";"Del Potro", "William", " Juan Martin ";"Verdasco", "John", " Fernando ";"Gonzalez", "Kevin", " Fernando ";"Wawrinka", "Brian", " Stanislas ";"Blake", "Ted", " James ";"Nalbandian", "Bill", " David ";"Robredo", "George", " Tommy ";"Cilic", "Bruce", " Marin ";"Stepanek", "Michael", " Radek ";"Almagro", "Pihilip", " Nicolas "})

and returns

{#VALUE!, #VALUE!, 3;#VALUE!, #VALUE!, 3;5, #VALUE!, #VALUE!;2, 2, 5;#VALUE!, #VALUE!, #VALUE!;#VALUE!, #VALUE!, #VALUE!;5, 2, 9;5, #VALUE!, 9;2, #VALUE!, #VALUE!;3, #VALUE!, #VALUE!;2, #VALUE!, #VALUE!;#VALUE!, 3, 3;#VALUE!, #VALUE!, #VALUE!;5, #VALUE!, #VALUE!;1, #VALUE!, 5}.

#### Step 2 - Convert numbers to true

The ISNUMBER function returns TRUE if value is a number and FALSE for everything else even errors which is very handy in this case, the search function returns #VALUE! error if a string is not found in a particular cell.

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

becomes

--(ISNUMBER({#VALUE!, #VALUE!, 3;#VALUE!, #VALUE!, 3;5, #VALUE!, #VALUE!;2, 2, 5;#VALUE!, #VALUE!, #VALUE!;#VALUE!, #VALUE!, #VALUE!;5, 2, 9;5, #VALUE!, 9;2, #VALUE!, #VALUE!;3, #VALUE!, #VALUE!;2, #VALUE!, #VALUE!;#VALUE!, 3, 3;#VALUE!, #VALUE!, #VALUE!;5, #VALUE!, #VALUE!;1, #VALUE!, 5}))

becomes

--({FALSE, FALSE, TRUE;FALSE, FALSE, TRUE;TRUE, FALSE, FALSE;TRUE, TRUE, TRUE;FALSE, FALSE, FALSE;FALSE, FALSE, FALSE;TRUE, TRUE, TRUE;TRUE, FALSE, TRUE;TRUE, FALSE, FALSE;TRUE, FALSE, FALSE;TRUE, FALSE, FALSE;FALSE, TRUE, TRUE;FALSE, FALSE, FALSE;TRUE, FALSE, FALSE;TRUE, FALSE, TRUE})

The MMULT function can't work with boolean values so we need to convert them into their numerical equivalents. TRUE - 1 annd FALSE - 0 (zero).

--({FALSE, FALSE, TRUE;FALSE, FALSE, TRUE;TRUE, FALSE, FALSE;TRUE, TRUE, TRUE;FALSE, FALSE, FALSE;FALSE, FALSE, FALSE;TRUE, TRUE, TRUE;TRUE, FALSE, TRUE;TRUE, FALSE, FALSE;TRUE, FALSE, FALSE;TRUE, FALSE, FALSE;FALSE, TRUE, TRUE;FALSE, FALSE, FALSE;TRUE, FALSE, FALSE;TRUE, FALSE, TRUE})

and returns

{0, 0, 1;0, 0, 1;1, 0, 0;1, 1, 1;0, 0, 0;0, 0, 0;1, 1, 1;1, 0, 1;1, 0, 0;1, 0, 0;1, 0, 0;0, 1, 1;0, 0, 0;1, 0, 0;1, 0, 1}

#### Step 3 - Sum values row-wise

MMULT(--(ISNUMBER(SEARCH(TRANSPOSE($G$2:$G$4), $B$3:$D$17))), {1;1;1})

becomes

MMULT({0, 0, 1;0, 0, 1;1, 0, 0;1, 1, 1;0, 0, 0;0, 0, 0;1, 1, 1;1, 0, 1;1, 0, 0;1, 0, 0;1, 0, 0;0, 1, 1;0, 0, 0;1, 0, 0;1, 0, 1}, {1;1;1})

and returns

{1; 1; 1; 3; 0; 0; 3; 2; 1; 1; 1; 2; 0; 1; 2}

#### Step 4 - Convert non-numerical values to corresponding row numbers

The following IF function returns the row number if number is 3, there are three strings that must match. FALSE returns "" (nothing).

IF(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE($G$2:$G$4), $B$3:$D$17))), {1; 1; 1})=3, MATCH(ROW($B$3:$D$17), ROW($B$3:$D$17)), "")

becomes

IF({1; 1; 1; 3; 0; 0; 3; 2; 1; 1; 1; 2; 0; 1; 2}=3, MATCH(ROW($B$3:$D$17), ROW($B$3:$D$17)), "")

becomes

IF({1; 1; 1; 3; 0; 0; 3; 2; 1; 1; 1; 2; 0; 1; 2}=3, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15}, "")

and returns

{"";"";"";4;"";"";7;"";"";"";"";"";"";"";""}.

#### Step 5 - Extract k-th smallest value in array

The SMALL function makes sure that a new value is returned in each row.

SMALL(IF(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE($G$2:$G$4), $B$3:$D$17))), {1; 1; 1})=3, MATCH(ROW($B$3:$D$17), ROW($B$3:$D$17)), ""), ROWS($A$1:A1))

becomes

SMALL({"";"";"";4;"";"";7;"";"";"";"";"";"";"";""}, ROWS($A$1:A1))

The ROWS function returns a new number because the cell reference expands as the formula is copied to cells below.

SMALL({"";"";"";4;"";"";7;"";"";"";"";"";"";"";""}, ROWS($A$1:A1))

becomes

SMALL({"";"";"";4;"";"";7;"";"";"";"";"";"";"";""}, 1)

and returns 4.

#### Step 6 - Return value

The INDEX function returns a value based on a row and column number.

INDEX($B$3:$D$17, SMALL(IF(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE($G$2:$G$4), $B$3:$D$17))), {1; 1; 1})=3, MATCH(ROW($B$3:$D$17), ROW($B$3:$D$17)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1))

becomes

INDEX($B$3:$D$17, 4, COLUMNS($A$1:A1))

becomes

INDEX($B$3:$D$17, 4, 1)

and returns "Davydenko" in cell F8.

### Download Excel *.xlsx file

multiple criteria lookup with multiple results.xlsx

### Lookup with multiple criteria and display multiple search results (VBA)

**Where to copy vba code**

- Copy vba code below
- Press Alt + F11
- Insert a new module
- Paste code into code window
- Return to Excel

**Array Formula in cell E9:**

=Searchtbl(F2:F4;A2:C16)

**How to create array formula**

- Select cell range E9:G11
- Type above array formula
- Press and hold Ctrl + Shift
- Press Enter once
- Release alla keys

Function Searchtbl(SrchRng As Variant, tbl As Variant) As Variant 'SrchRng must have equal number of cells as headers in table Dim i, r, c As Single Dim tempArray() As Variant ReDim tempArray(tbl.Columns.Count - 1, 0) tbl = tbl.Value SrchRng = SrchRng.Value For r = LBound(tbl, 1) To UBound(tbl, 1) i = 0 For c = LBound(SrchRng) To UBound(SrchRng) If InStr(UCase(tbl(r, c)), UCase(SrchRng(c, 1))) = 0 Then i = 0 Exit For Else i = i + 1 End If Next c If i = UBound(tbl, 2) Then For c = LBound(tempArray, 1) To UBound(tempArray, 1) tempArray(c, UBound(tempArray, 2)) = tbl(r, c + 1) Next c ReDim Preserve tempArray(UBound(tempArray, 1), UBound(tempArray, 2) + 1) i = 0 End If Next r ReDim Preserve tempArray(UBound(tempArray, 1), UBound(tempArray, 2) - 1) Searchtbl = Application.Transpose(tempArray) End Function

**Download excel file *.xls**

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 and display all cells that contain multiple search strings

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

Search for multiple text strings in column – AND logic

Question: How do I search a list for two text strings and return a list with where both strings occurs? […]

Return multiple matches with wildcard vlookup

Mr.Excel had a "challenge of the month" June/July 2008 about Wildcard VLOOKUP: "The data in column A contains a series […]

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

RU asks: Can you please suggest if i want to find out the rows with fixed value in "First Name" […]

Wildcard lookups and include or exclude criteria

This article demonstrates three different ways to filter a data set if a value contains a specific string and if […]

Row is returned if both search strings are found on the same row

Question: I want to search two columns with two search strings? The strings can be anywhere in these two columns […]

Minh Hung asks: Hello Mr Oscar I have the matter to create a megaformula to categorize my list. For short […]

### 16 Responses to “Search columns for a string and return records – AND 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

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

You are a genius. Formulas are simple, and easy to understand.

Could this be done to filter for 10 criteria?

10 criteria in each column? 10 criteria in all columns?

Hi,

i had a similar query and i believe your example is the way forward for me but i can't get my search to work.

like your example i'm searching 3 columns but in a table of 8 colums and the search function is on a seperate tab. there is also a gap between the columns i'm searching. i also decided not to name the ranges as they may change. are any of the above affecting the formala or is it just me?

ignore my last comment... it works a treat!! (i put in one too many $ signs!!)

Thanks!

I second G's question: can this be done for more than 3?

i.e.

(Instead of last name, middle, first)

customer#, cust name, appt date, appt time, venue, coordinator, assistant

Thanks for putting this up!

D,

See this blog post: https://www.get-digital-help.com/2009/10/01/lookup-with-multiple-criteria-and-display-multiple-search-results-using-excel-formula-part-4/

I applied this in my spreadsheet and worked perfectly well.

However, I noticed that if a cell in the search range is blank, it does not include it in the results. In your example, if B2 was blank instead of having the value "Ted." that name would not show up in the search results (E9:E11).

Is there a work around for this? The spreadsheet I have may have blank cells but I need them to show in the results page.

Thanks!!!

THis is great!!!

What if I want to search using 4 criteria? Can you please help me?

Thanks!

Min,

I have changed the formula. I think the new formula is easier. I have also written an explanation, perhaps with help from explanation, you can now add a fourth criterion yourself? Otherwise, comment here again.

I uploaded a new file, as well.

Hi,

Your articles are really helpful! 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.

Regards

RU,

Read this post: Lookup with multiple criteria and display multiple unique search results (array formula)

This solution was elegant and consistent! The only issue I am facing, (or so I've been told by an associate) is that as the number of entries increases, it would be best to write this functionality into a module(?) for the sake of performance. Any ideas, solutiobns or resources would be appreciated!! Thank you!!

Jim,

I have added a custom function to this post. I have not done any performance testing.

Oscar = Legend / Demigod

Thanks for your time and skill explaining this for all us mortals.

Stuart,

Thank you for commenting!

The explanations are not that hard to do. Select a cell containing a formula. Click in the formula bar. Press F9 and the formula is converted into a value. This also works for array formulas.