## Extract records where all criteria match if not empty

*Article updated on February 17, 2018*

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

### Download Excel *.xlsx file

**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 row number of a reference

**COUNT(**value1, [value2]**)
**Counts the number of cells in a range that contain numbers

**SMALL(**array, k**)** returns the k-th smallest row number in this data set.

**ISNUMBER(**value)

Checks whether a value is a number and returns TRUE or FALSE

**SUM(**number1, [number2],**)
**Adds all the numbers in a range of cells

**SEARCH()** Returns the number of the character at which a specific character or text string is first found, reading left to right (not case sensitive)

### Read more articles about this topic

The following article shows you how to VLOOKUP and return multiple values, the lookup value must match the entire cell value.

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Search for a cell value in a dataset:

Search for a cell value in a dataset

This blog article is one out of five articles on the same subject. Search for a cell value in an […]

How to look for values in a column that contain two text strings:

Search for multiple text strings in multiple cells

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

Search two columns for two text strings. The strings can be anywhere in these two columns but both text strings have to be found in different cell values or a single cell value and be on the same row to match:

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

Search for three text strings in three columns, one text string in each column. Return values in which all three cases are found:

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

Question: Can expand this equation set into more than two colums of data, say if I had a first, middle […]

Search for **multiple text strings** in **multiple columns**, one text string in each column. Return values in which **all text strings match**:

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

Search for a single text string in a **single** column and return multiple matches.

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 a text string in a **single** column and return multiple corresponding values.

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 a **single** column and return multiple corresponding values.

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 a text string in **multiple** columns and return corresponding values.

Search for a text string and return multiple adjacent values

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 […]Search and display all cells that contain all search strings in excel

Jerome asks, in this blog post Search for multiple text strings in multiple cells in excel : If the list […]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" […]Split search value using delimiter and search for each substring

Anil asks: I have A1(anil singh raj) It can be anything Like A1(singh raj anil) I want return value in […]### 29 Responses to “Extract records where all criteria match if not empty”

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