In a previous blog post Extract cell values in a range using a criterion in excel I provided a formula to lookup values in range using one criterion and return (if possible) multiple adjacent values.

In this blog post I´ll show you how to lookup values in range using two or more criteria and return (if possible) multiple adjacent values using an array formula.

If you can rearrange your data, read this post:
Lookup values in a range using two or more criteria and return multiple matches in excel, part 2
You can use a lot easier array formula if you rearrange your data.

Array formula in B22:

=INDEX(tbl, SMALL(IF(COUNTIF(search_col, tbl)>0, ROW(tbl)-MIN(ROW(tbl))+1), ROW(A1)), (SMALL(IF(COUNTIF(search_col, tbl)>0, ((ROW(tbl)-MIN(ROW(tbl))+1)+(COLUMN(tbl)-MIN(COLUMN(tbl))+1)/16384)*1), ROW(A1))-SMALL(IF(COUNTIF(search_col, tbl)>0, ROW(tbl)-MIN(ROW(tbl))+1), ROW(A1)))*16384+1) + CTRL + SHIFT + ENTER

copied down as far as needed.

Read an explanation of this array formula here:
Explaining a formula: Lookup values in a range using two or more criteria and return multiple matches in excel

Named ranges

tbl (B3:G8)
search_tbl (B18:B19)
What is named ranges?

How to implement array formula to your workbook

Change the named ranges. Increase search_col range to add more lookup values. The bolded value is the specified (adjacent) column number you want extract from a matching value.

Download excel sample file for this tutorial.

Filter-cells-using-two or more-criteria-in-excel.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