Lookup values in a range using two or more criteria and return multiple matches in excel, part 2
In this post we are going to extract multiple text values. We are looking for names and the criteria are two or more states (cell B18:B19) and two or more months (cell D18:D19).
But first there is something we can do with the data. See picture below.
Rearrange data
The data could have been better arranged like:
Date | State | Name
Searching data arranged as above is easy. You have these options:
- Excel table
- Excel Pivot table
- Array formula
Array formula
Array formula in B26:
Copy cell B26 and paste it down as far as needed.
Named ranges
search_states (B22:B23)
search_dates (D22:D24)
What is named ranges?
What if you don´t want to rearrange your data? Think again!
Ok, so you don´t want to rearrange your data
Array formula in B22:
Named ranges
tbl (B3:G8)
search_states (B18:B19)
search_dates (D18:D19)
What is named ranges?
If you compare the two array formulas you realize that the last formula is more prone to errors, hard to troubleshoot and somewhat?!? complicated. Rearranging data is not complicated.
Download excel sample file for this tutorial.
Filter-cells-using-two-or-more-criteria.xls
(Excel 97-2003 Workbook *.xls)
Functions in this article:
COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition
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
SMALL(array,k) returns the k-th smallest row number in this data set.
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
MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text
ROW(reference) returns the rownumber of a reference
COLUMN(reference)
returns the column number of a reference
Related blog posts
- Lookup values in a range using two or more criteria and return multiple matches in excel
- Explaining a formula: Lookup values in a range using two or more criteria and return multiple matches in excel
- Vlookup with 2 or more lookup criteria and return multiple matches in excel
- Lookup a value in a list and return multiple matches in excel
- Lookup two index columns using min max values and a date range as criteria
Welcome! I am Oscar and here to help you! Feel free to comment and ask excel questions.Don´t forget to add my
January 4th, 2011 at 11:05 pm
Thanks so much for this! It has been very helpful. I wonder if I can ask a question about it, though. It looks like the countif criterias are acting like "OR" statements. I wonder if we could do an "AND" statement? Using your above example, I would only want to find the names of those who had visited South Carolina in February of 2009 - both criteria must be true for a positive return. What do you think?
January 5th, 2011 at 8:45 am
Julie,
I am guessing you are talking about the first array formula in cell B26, in this article.
Yes, the countif formulas are acting like "OR", but only between values in the named range, inside each countif.
Example:
First countif function
COUNTIF(search_states, $C$4:$C$17)
Countif("Maine","South Carolina", "Kansas", "Maine", "South Dakota", "Montana", "Delaware", "Kentucky", "South Carolina", "South Dakota", "Oregon", "South Carolina", "Oregon", "Delaware", "Maine", "Kansas")
If any of the values in the named range search_states are found, the returning value would be 1. The formula returns this array (0, 1, 0, 0, 0, 0, 1, 0, 0, 1, 0, 0, 1, 0)
Second countif function
=COUNTIF(search_dates;$B$4:$B$17)
The formula returns this array (1, 1, 1, 1, 0, 0, 0, 0, 0, 1, 1, 1, 1, 1)
COUNTIF(search_states, $C$4:$C$17)*COUNTIF(search_dates, $B$4:$B$17)
becomes
(0, 1, 0, 0, 0, 0, 1, 0, 0, 1, 0, 0, 1, 0)*(1, 1, 1, 1, 0, 0, 0, 0, 0, 1, 1, 1, 1, 1)
and returns this array (0, 1, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 1, 0)
In order to only select names who had visited South Carolina in February of 2009, you only need to change values and adjust ranges in the named ranges "search_dates" and "search_states".
January 21st, 2011 at 3:23 pm
Thanks so much for the explanation! It is working perfectly now in my spreadsheet. I wonder, though... is there a way to change the #NUM results to just a blank?
January 21st, 2011 at 10:47 pm
Julie,
Excel 2007:
IFERROR(value;value_if_error)
Returns value_if_error if expression is an error and the value of the expression itself otherwise
January 25th, 2011 at 10:15 pm
IFERROR doesn't seem to want to work with this formula... here is what I have:
=INDEX(CourseEvals!$Q$2:$Q$65000, SMALL(IF(COUNTIF('Compilation Sheet'!$G$3,CourseEvals!$A$2:$A$65000)*COUNTIF('Compilation Sheet'!$G$5,CourseEvals!$C$2:$C$65000)*COUNTIF('Compilation Sheet'!$G$7,CourseEvals!$E$2:$E$65000), (ROW(CourseEvals!$Q$2:$Q$65000)-MIN(ROW(CourseEvals!$Q$2:$Q$65000))+1), ""), ROW(A60)))
I am running Office 2010 on a Windows 7 machine if that makes any difference.
Thanks!
January 25th, 2011 at 10:23 pm
Julie,
How to remove #num errors from the array in this post:
=IFERROR(INDEX($D$4:$D$17, SMALL(IF(COUNTIF(search_states, $C$4:$C$17)*COUNTIF(search_dates, $B$4:$B$17), (ROW($B$4:$B$17)-MIN(ROW($B$4:$B$17))+1), ""), ROW(A1))),"")
Your array formula:
=IFERROR(INDEX(CourseEvals!$Q$2:$Q$65000, SMALL(IF(COUNTIF('Compilation Sheet'!$G$3,CourseEvals!$A$2:$A$65000)*COUNTIF('Compilation Sheet'!$G$5,CourseEvals!$C$2:$C$65000)*COUNTIF('Compilation Sheet'!$G$7,CourseEvals!$E$2:$E$65000), (ROW(CourseEvals!$Q$2:$Q$65000)-MIN(ROW(CourseEvals!$Q$2:$Q$65000))+1), ""), ROW(A60))),"")
January 25th, 2011 at 10:34 pm
Ah Ha!!! Thank you, thank you, thank you. My spreadsheet is lovely now thanks to you.
August 12th, 2011 at 2:29 pm
Hello Oscar,
You are really enlightening!
I try to use your formula, as you wrote "Array formula in B22" as an example for my problem, but I can't fit it in.
The difference is that,
I must combine the results of column (expl. B with a row above a table)
Imagine
B 1 2 3 4 5
bole1 24 27 10 43 63
bole2 25 09 98 12 56
bole3 33 12 39 00 23
To combine I mean these 2 criteria and return column results
ex. bole2 and 3 result 98
Could you help?
August 12th, 2011 at 2:55 pm
Oscar,
A small detail, I have office 2003
August 13th, 2011 at 9:09 am
Russel,
I think this post answers your question:
http://www.get-digital-help.com/2009/06/24/looking-up-data-in-a-cross-reference-table-in-excel/
August 13th, 2011 at 5:24 pm
Oh! It is simpler than I thought!I guess that my criteria of searching it, weren't right!
Thank you very much, Oscar!!!
A small tricky question,as add on to this...if the price in the last cell is in group form, something like 6-20, in our example
B 1 2 3 4 5 6-20
bole1 24 27 10 43 63 45
bole2 25 09 98 12 56 32
bole3 33 12 39 00 23 11
how can we put in our criteria, bole2 and 14 and appear us the right result( 32)...i mean to put in cell a group of numbers and they will appeared as a sum of individuals, to drag?
What do you believe?Is this possible?
Thank you again,your answer really helped me a lot!
August 16th, 2011 at 12:00 pm
Russel,
I am sorry for not answering your question.
Here is the answer:
Download excel file
russel.xls
Excel 97-2003 *.xls
August 16th, 2011 at 2:08 pm
Oscar, thank you very much!!!I really appreciate your help!
It was really a clever way of doing this!(I have much to learn)
Thank again, and have a nice holiday!
December 14th, 2011 at 10:56 am
Hi Oscar,
First of all thanks for creating this website/blog for us to learn and really appreciate your sharing of your knowledge and I personally find this is very usefull to our day to day work.
Come to my qeustions;
If I've a range of Date in the data and I need to return multiple data from the lookup with multiple criteria and data in between the user input from date to date, how should I modified your formula.
Take your example If the Date is from 1-Jan-2011 till 31-Jan-2011, below are my look up criteria;
1. Search State
2. From date
3. To date
So the 2 and 3 is to specified a date range in the data for lookup
December 18th, 2011 at 10:15 pm
Patrick,
download file:
Patrick.xlsx
December 20th, 2011 at 3:44 am
Hi Oscar,
Thanks you so much and it works like a charm...