In previous posts I provided formulas on how to lookup one value in a list and return multiple matches.

• Using array formula to look up multiple values in a list
• How to return multiple values using vlookup in excel
• Search for multiple text strings in multiple cells in excel

Now I´ll show you how to lookup two or more values in a list and return (if possible) multiple matches.

Array formula in B13:

=INDEX(tbl, SMALL(IF(COUNTIF(search_tbl, INDEX(tbl, , 1, 1))>0, ROW(tbl)-MIN(ROW(tbl))+1), ROW(A1)), 2) + CTRL + SHIFT + ENTER copied down as far as needed.

Array formula in B13 without named ranges:

=INDEX($C$2:$C$6, SMALL(IF(COUNTIF($B$9:$B$10,$B$2:$B$6>0, ROW($B$2:$C$6)-MIN(ROW($B$2:$C$6))+1), ROW(A1))) + CTRL + SHIFT + ENTER copied down as far as needed.

I have to admit that I am not using Vlookup at all in this array formula. Vlookup looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify. The column you specify is bolded in the above array formula.

Here is an alternative array formula:

=INDEX($C$2:$C$6, SMALL(IF(($B$9=$B$2:$B$6)+($B$10=$B$2:$B$6), ROW($B$2:$B$6)-1, ""), ROW(A1))) + CTRL + SHIFT + ENTER. This formula is easier to understand but what if the number of lookup values increases to let´s say 15 or more. The alternative array formula increases in size to this:

=INDEX($C$2:$C$6, SMALL(IF(($B$9=$B$2:$B$6)+($B$10=$B$2:$B$6)+($B$11=$B$2:$B$6)+($B$12=$B$2:$B$6)+($B$13=$B$2:$B$6)+($B$14=$B$2:$B$6)+($B$15=$B$2:$B$6)+($B$16=$B$2:$B$6)+($B$17=$B$2:$B$6)+($B$18=$B$2:$B$6)+($B$19=$B$2:$B$6)+($B$20=$B$2:$B$6)+($B$21=$B$2:$B$6)+($B$22=$B$2:$B$6)+($B$23=$B$2:$B$6), ROW($B$2:$B$6)-1, ""), ROW(A1))) + CTRL + SHIFT + ENTER.

The first array formula is easier to customize if more lookup values are added. It is also smaller in size when many lookup values are used. The first array formula is not case-sensitive. To create a case-sensitive formula use FIND instead of SEARCH.

Named ranges

tbl (B2:C6)
search_tbl (B9:B10)
What is named ranges?

How to increase the number of search strings

Change the named range search_tbl.

Download excel example file

Vlookup-with-two-search-criteria-and-return-multiple-matches.xls
(Excel 97-2003 Workbook *.xls)

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 rownumber of a reference

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

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)

TRANSPOSE(array)
Converts a vertical range to a horizontal range, or vice versa.

Related posts:

1. Lookup a value in a list and return multiple matches in excel
2. Lookup values in a range using two or more criteria and return multiple matches in excel
3. How to return multiple values using vlookup in excel
4. Lookup with multiple criteria and display multiple search results using excel formula, part 4
5. Lookup with multiple criteria and display multiple search results using excel formula
6. Lookup with multiple criteria and display multiple search results using excel formula, part 3
7. Lookup with multiple criteria and display multiple search results using excel formula, part 2
8. Lookup two index columns returning multiple matches in excel
9. Vlookup of three columns to pull a single record
10. Sum adjacent values using multiple lookup text values in a column in excel