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

### 21 Responses to “Lookup values in a range using two or more criteria and return multiple matches in excel, part 2”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

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

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?

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".

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?

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

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!

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))),"")

Ah Ha!!! Thank you, thank you, thank you. My spreadsheet is lovely now thanks to you.

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?

Oscar,

A small detail, I have office 2003

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/

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!

Russel,

I am sorry for not answering your question.

Here is the answer:

Download excel filerussel.xls

Excel 97-2003 *.xls

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!

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

Patrick,

download file:

Patrick.xlsx

Item Start Date End Date Value

A 01-Apr-11 02-Feb-12 2

A 03-Feb-12 01-Mar-12 3

A 02-Mar-12 31-Dec-13 4

A 01-Jan-14 31-Jan-14 5

B 09-Jan-13 04-Apr-14 6

B 05-Apr-14 07-Feb-15 7

B 08-Feb-15 01-May-16 8

B 02-May-16 01-Jun-16 9

Item Date Value

A 30-Apr-12

A 05-Feb-12

A 30-Jan-14

B 07-Apr-14

B 20-Feb-15

Dear Sir,

Please help for the above value column.

Hi Oscar,

Thanks you so much and it works like a charm...

Using your rearranged data example is it possible to output unique records? For example, if the name 'Johnson' also had record for 2009 March that there would only be one instance in the search results for that name.

BrianM

Yes, it is possible.

=INDEX($D$4:$D$17, SMALL(IF(COUNTIF(search_states, $C$4:$C$17)*COUNTIF(search_dates, $B$4:$B$17)*(COUNTIF($B$25:B25, $D$4:$D$17)=0),(ROW($B$4:$B$17)-MIN(ROW($B$4:$B$17))+1), ""), 1))

Hi Oscar,

Thank you for all of this information! I do have a quick question and hopefully you have an easy solution...

What if on your arranged data example, 2009 March (next to Maine) was misspelled? If it read "2009 Mach", then Lopez would not show up.

I am having similar issues looking up business names that do not all match (example: xxLLC vs. xxLLC. vs. xxxLLC) and I am SOOO stuck right now!

Thanks for any help you can give!

Angie,

You need some kind of "fuzzy" lookup, read this post:

http://www.get-digital-help.com/2011/04/04/excel-udf-fuzzy-lookups/