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.
Get Excel *.xlsx file
Filter records category
This article demonstrates how to extract records/rows based on two conditions applied to two different columns, you can easily extend […]
Lookup with criteria and return records.
This article explains different techniques that filter rows/records that contain a given text string in any of the cell values […]
Excel categories
39 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.
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))
Is it to late to ask questions?
In the
the
>= Appt date1
<= Appt date2
do nothing.
could that be changed to be 2 searches?
thanks
Sorry some how lost the information I was entering.
I have a question using
Lookup-with-multiple-criteria-and-display-multiple-search-results-using-excel-formula-part-4-john.xlsx
in the list where search informaion is entered. 2 of them do not work.
Customer# works ok
Cust name works ok
>= Appt date1 does not work
<= Appt date2 does not work
time works ok
Venue works ok
Coordinator works ok
Assistant works ok
what can I do to fix them?
they are not listed in the results
Row Customer#- Cust name- Appt date- Appt time- Venue - Coordinator- Assistant
Could they be changed to 2 more columns to search and add the results to the results list?
thanks, dean thatcher
HI
Note Appt date works ok in
Lookup-with-multiple-criteria-and-display-multiple-search-results-using-excel-formula-part-4-johnv2_table
hi
How can 3 more search Columns and 3 more results Columns be added to?
Lookup-with-multiple-criteria-and-display-multiple-search-results-using-excel-formula-part-4-johnv2.xlsx
added searches needed would be:
Line
Description
Daily Totals
thanks
hi
How can 3 more search Columns and 3 more results Columns be added to?
Lookup-with-multiple-criteria-and-display-multiple-search-results-using-excel-formula-part-4-johnv2.xlsx
added searches needed would be:
Line
Description
Daily Totals
thanks
hi
How can 3 more search Columns and 3 more results Columns be added to?
Lookup-with-multiple-criteria-and-display-multiple-search-results-using-excel-formula-part-4-johnv2.xlsx
added searches needed would be:
Line
Description
Daily Totals
Hi Oscar! I really admire your work and all the effort you've put on this website. I have used your worksheet to create a comparison between products of two companies. But i am stuck at one point.
I need to find the closest match of a number (right now it is searching exact match only) on a certain parameter let's say instead of date i have some numbers. How do i do it?
Kindly help me accomplish this task!
Hello, Oscar! How are you? I hope you're healthy. I thank you for your site and I am enjoying your system applied in the link https://www.get-digital-help.com/lookup-with-multiple-criteria-and-display-multiple-search-results-using-excel-formula-part-4/, however, I have a question about how to fix a problem that it has generated. For example, in the data entry area, if one of the cells is empty (no data, in cell G5, for example) then the cells shows an error "#Num!" in all cells of that row in the search results area or the line information is hidden. Would you know how to help me? Thanks!