## Extract records where all criteria match if not empty

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

*A question asked in this post:
Lookup with multiple criteria and display multiple search results using excel formula, part 3*

**Answer:**

**Array formula in B20:**

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 B20

#### Step 1 - Compare criteria to data

The equal sign allows you to compare values, the resulting array contains TRUE or FALSE.

($B$3:$H$12=$B$16:$H$16)*1

becomes

({1, "Taylor", 39965, 0.333333333333333, "-", "Robinson", "Lee";2, "Anderson", 39965, 0.375, "-", "Clark", "Walker";3, "Thomas", 39965, 0.416666666666667, "-", "Rodriguez", "Hall";4, "Jackson", 39965, 0.458333333333333, "-", "Robinson", "Lee";5, "White", 39965, 0.541666666666667, "-", "Clark", "Walker";6, "Harris", 39966, 0.333333333333333, "-", "Rodriguez", "Hall";7, "Martinez", 39966, 0.375, "-", "Robinson", "Lee";8, "Thompson", 39966, 0.416666666666667, "-", "Clark", "Walker";9, "Garcia", 39966, 0.458333333333333, "-", "Rodriguez", "Hall";10, "Martinez", 39966, 0.541666666666667, "-", "Robinson", "Lee"}={0, 0, 39966, 0, 0, "Rodriguez", 0})*1

becomes

{FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, TRUE, FALSE, FALSE, TRUE, FALSE;FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, TRUE, FALSE, FALSE, TRUE, FALSE;FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE}*1

The MMULT function can't work with boolean values so in order to get that working we must multiply the array with 1.

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

#### Step 2 - Add values row-wise

MMULT(($B$3:$H$12=$B$16:$H$16)*1,{1;1;1;1;1;1;1})

becomes

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

and returns

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

#### Step 3 - Compare sum with the number of criteria

We know a record match if the number of criteria equals the sum returned from the MMULT function. The COUNTA function lets you count non empty cells in a given cell range.

MMULT(($B$3:$H$12=$B$16:$H$16)*1, {1;1;1;1;1;1;1})=COUNTA($B$16:$H$16)

becomes

{0;0;1;0;0;2;1;1;2;1}=COUNTA($B$16:$H$16)

becomes

{0;0;1;0;0;2;1;1;2;1}=2

and returns

{FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}.

#### Step 4 - Replace TRUE with corresponding row number

TheĀ IF functionĀ allows you to return a value if the logical expression evaluates to TRUE and another if FALSE.

IF(MMULT(($B$3:$H$12=$B$16:$H$16)*1, {1;1;1;1;1;1;1})=COUNTA($B$16:$H$16), MATCH(ROW($B$3:$B$12), ROW($B$3:$B$12)), "")

becomes

IF({FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}, MATCH(ROW($B$3:$B$12), ROW($B$3:$B$12)), "")

becomes

IF({FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}, {1;2;3;4;5;6;7;8;9;10}, "")

and returns

{"";"";"";"";"";6;"";"";9;""}

#### Step 5 - Extract the k-th smallest row number

The SMALL function lets you get the k-th smallest number in an array. SMALL( array, k)

SMALL(IF(MMULT(($B$3:$H$12=$B$16:$H$16)*1, {1;1;1;1;1;1;1})=COUNTA($B$16:$H$16), MATCH(ROW($B$3:$B$12), ROW($B$3:$B$12)), ""), ROWS($A$1:A1))

becomes

SMALL({"";"";"";"";"";6;"";"";9;""}, ROWS($A$1:A1))

The ROWS function returns the number of rows in a cell reference, this particular cell reference is expanding when the cell is copied to cells below.

SMALL({"";"";"";"";"";6;"";"";9;""}, 1)

and returns 6.

#### Step 6 - Return value

The INDEX function returns a value from a cell range or array based on a row and column number.

INDEX($B$3:$H$12, SMALL(IF(MMULT(($B$3:$H$12=$B$16:$H$16)*1, {1;1;1;1;1;1;1})=COUNTA($B$16:$H$16), MATCH(ROW($B$3:$B$12), ROW($B$3:$B$12)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1))

becomes

INDEX($B$3:$H$12, 6, COLUMNS($A$1:A1))

becomes

INDEX($B$3:$H$12, 6, 1)

and returns "6" in cell B20.

### Download Excel *.xlsx file

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

Extract all rows from a range that meet criteria in one column

Lookup with criteria and return records.

Search for a text string in a data set and return multiple records

This article explains different techniques that filter rows/records that contain a given text string in any of the cell values […]

Extract all rows that contain a value between this and that

Question: I have a list and I want to filter out all rows that have a value (Column C) that […]

Extract records containing digits [Formula]

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

Extract records between two dates

Question: How to filter rows using dates? Answer: In this post I will describe how to: Filter rows using array […]

Extract records containing negative numbers

Table of Contents Extract negative values and adjacent cells (array formula) Extract negative values and adjacent cells (Excel Filter) Array […]

Filter records based on a date range and a text string

Murlidhar asks: How do I search text in cell and use a date range to filter records? i.e st.Dt D1 […]

### 31 Responses to “Extract records where all criteria match if not empty”

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

Hi Oscar, love your blogs! great tutorials. Keep them coming!

Chrisham,thanks!

Thank you for the example. However I stillhave a problem that do not fit on your examples.

I have a table of 3 coloms (Security name, date, price) and I have to find the price a a security at a certain date in a table that contain many securities and prices for this securities for different dates. If I work with vlookup or Index-match I got only the first price for a certain securities. So I am not able to find the price of a securities that match both the name of the securities and the date. Could you advice if there is any way to overcome?

Paolo,

See this blog post: https://www.get-digital-help.com/2010/02/11/match-two-criteria-and-return-multiple-rows-in-excel/

Hi Oscar,

I've got a problem using your suggested approach. I have a list which I need to search from. I have three search criteria, and wish to output a fourth item. For each combination of the search criteria, there will only be one matching result.

I'm trying to use a series of search criteria (arranged in a table - with one row for each search instance, one column for each criterion) which is located on a different worksheet within the same workbook to the data table from which I need to extract the values.

The first search returns the desired result, however the next row returns #NUM error. In order to replicate the formula on the second and subsequent rows I've used a straight forward copy/past - the formula appears to be an Array one still (preceded and suceeded by {} as appropriate).

Do you have any suggestions which might help?

Do you have any suggestions on what might be causing this?

Oscar,

You are doing exacting what I have been trying to do for my Excel file but I cannot seem to get mine to work for some reason. Do you think you could take a look at my file if you have a chance. I would greatly appreciate it.

Bryant,

https://www.get-digital-help.com/contact/

Hi, Can you do this with a greater than date?

Arielle,

Array formula in cell B25:

=SMALL(IF((COUNTIF(Search_customer, Customer)+COUNTIF(Search_cust_name, Cust_name)+COUNTIF(Search_Appt_time, Appt_time)+COUNTIF(Search_venue, Venue)+COUNTIF(Search_Coordinator, Coordinator)+COUNTIF(Search_Assistant, Assistant))*COUNTIF(Search_Appt_date, "<"&Appt_date), ROW(Customer)-MIN(ROW(Customer))+1), ROWS(B24:$B$24)) + CTRL + SHIFT + ENTER. Copy cell b25 and paste it to the cells below, as far as needed.

Hi,

Can you search within the whole excel workbook instead of just the sheet?

thanks

Also, what if I want to search 2 or more words within a column but they are not together (ex. 1 of the cell stated "hamburger, hotdog, soda", can i search for both hamburger and hotdog if are not side by side?)

thanks for your help

Oscar, great tutorial but i have one question.

In your spreadsheet at the top of the article your search results show a 3rd line that show #NUM1 for each answer.

Is there a way to show this as a blank cell instead?

cheers

Mark,

Yes!

=IFERROR(

array_formula, "")DonĀ“t forget to enter it as an array formula. It works only in excel 2007 and 2010.

sorry #NUM1 should be #NUM!

cheers

Hi Oscar,

I'm trying to implement something similar, but using the customer name and between 2 different date ranges, so will need to use >= Appt date1 and <= Appt date2. I've been trying and trying but keep getting the good old #NUM! error!!!

Please help!

Thank you!!!

John

John,

See attached file:

Lookup-with-multiple-criteria-and-display-multiple-search-results-using-excel-formula-part-4-john.xlsx

Hi Oscar,

I really appreciate you getting back to me. Thanks for the file but testing the formula is not producing the results I expect. Let's just simplify the formula and use only the following 3 criteria:

=IFERROR(SMALL(IF(ISNUMBER(SEARCH(Search_cust_name,Cust_name)*COUNTIFS(Appt_date,">="&Search_Appt_date1)*COUNTIFS(Appt_date,"= Search_Appt_date1 AND <= Search_Appt_date2. The string in Search_cust_name MUST match exact. The way the formula is right now still generates results if part of the Search_cust_name parameter matches and even worst if all the criteria values are blanks.

I hope I am explaining myself correctly. Your assistance will be forever appreciated!!!

Thank you,

John

Sorry, that didn't post correctly. I'm going to try again...

Please ignore previous post as it got truncated. Hopefully this will post properly:

Hi Oscar,

I really appreciate you getting back to me. Thanks for the file but testing the formula is not producing the results I expect. Let's just simplify the formula and use only the following 3 criteria:

=IFERROR(SMALL(IF(ISNUMBER(SEARCH(Search_cust_name,Cust_name)*COUNTIFS(Appt_date,">="&Search_Appt_date1)*COUNTIFS(Appt_date,"= Search_Appt_date1 AND <= Search_Appt_date2. The string in Search_cust_name MUST match exact. The way the formula is right now still generates results if part of the Search_cust_name parameter matches and even worst if all the criteria values are blanks.

I hope I am explaining myself correctly. Your assistance will be forever appreciated!!!

Thank you,

John

Hi Oscar,

Just wanted to let you know that my issue has been resolved so please disregard. I was able to incorporate your idea and nested a few other formulas to control the accuracy of the matching results that I expect. Once again thank you for your time as it is very much appreciated!

John

John,

I am going to answer your question anyway. The following formula filters records that exactly match (case insensitive) a criterion or criteria. An empty criteria is not calculated.

Array formula in cell B25:

See attached file:

Lookup-with-multiple-criteria-and-display-multiple-search-results-using-excel-formula-part-4-john.xls

Sorry Oscar but I must be providing a special tag in my post that is messing it all up and it doesn't make sense.

To clarify, let's just simplify the formula so it only checks for an exact match of the Search_cust_name AND between Search_Appt_date1 AND Search_Appt_date2. The way the formula is right now still generates results if part of the Search_cust_name parameter matches and even worst if all the criteria values are blanks.

Please advise and my apologies for the confusion with all the messed up posts!

John

Hi Oscar,

Thank you very much for that solution. it solved my problem !

Just one more question, and I am not sure if the above solution will work here. The problem is with regards to analysing the remaining life of my inventory based on the quantity available and the monthly consumption. Its as follows :

Assuming today's date is 1st of September. I have 20 packets of cheese and the average monthly consumption is 4 packets. Ideally if the shelf life of those 20 packets is 6 months or more, then I can safely say that the total quantity will last for 5 months (20 divided by 4).However, with a slight twist in the data, assume that out of those 20 packets, 6 packets expire on 15 Oct, 7 packets expire on 10 Nov, and the remaining 6 on 15 December. Now I know that the total inventory is not gonna last for 5 months due to various expiry dates. If I calculate this manually, I come to know that the quantity will last for 3 and a half months.

How can i put all this in a formula to get the right answer ?

Please advise.

Thanks

Haroun

Haroun,

See this post:

Inventory consumption

[...] in Count values, Dates, Excel on Oct.05, 2012. Email This article to a Friend Haroun asks:The problem is with regards to analysing the remaining life of my inventory based on the quantity [...]

[...] Search Formula Hi, I wonder whether someone may be able to help me please. From this site Lookup with multiple criteria and display multiple search results using excel formula, part 4 | Get ... I've put together this formula, which works as expected: [...]

Is there a way to create a multiple search field in excel through VBA, which would allow me to enter 3 or 4 different searches at one time and have that data populate in order to compare them. The 3 or 4 different searches are coming from a list of 50 different things, which is why i would like to create a search to narrow it down. Thanks!

=IFERROR(INDEX(Case, SMALL(IF(MMULT((Case=$B$4:$K$4)*1, {1;1;1;1;1;1;1;1;1;1})=COUNTA($B$4:$K$4), MATCH(ROW(Case), ROW(Case)), ""),ROWS($B$2:B2)), COLUMNS($B$2:B2)), "")

here is my code.

I would like to ask why even though it refers to case(Table), when I add new data to table it can't be found in the search.

even when it's inside the table?

Chiang

The following workbook contains an Excel defined Table:

https://www.get-digital-help.com/wp-content/uploads/2009/10/Lookup-with-multiple-criteria-and-display-multiple-search-results-using-excel-formula-part-4-johnv2_table.xlsx

I would like to ask why even though it refers to case(Table), when I add new data to table it can't be found in the search.The search seems to work when I add new records to the table, see above workbook.

Is it possible to make it so that it will search for partial text matches too?

Thank you

Nigel,

Yes, it is possible.

The following array formula in cell B20 returns records that have at least a partial match:

=INDEX($B$3:$H$12, SMALL(IF(MMULT(ISNUMBER(SEARCH($B$16:$H$16, $B$3:$H$12))*($B$16:$H$16<>""), {1;1;1;1;1;1;1})=COUNTA($B$16:$H$16), MATCH(ROW($B$3:$B$12), ROW($B$3:$B$12)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1))