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

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

How the array formula works (without named ranges) in cell B13

=INDEX($C$2:$C$6, SMALL(IF(COUNTIF($B$9:$B$10,$B$2:$B$6), ROW($B$2:$C$6)-MIN(ROW($B$2:$C$6))+1), ROW(A1)))

Step 1 - Filter values

=INDEX($C$2:$C$6, SMALL(IF(COUNTIF($B$9:$B$10,$B$2:$B$6), ROW($B$2:$C$6)-MIN(ROW($B$2:$C$6))+1), ROW(A1)))

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition.

COUNTIF($B$9:$B$10,$B$2:$B$6)

becomes

COUNTIF({"Pen";"Paper"},{"Pen";"Eraser";"Paper";"Pen";"Paper clip"})

and returns {1;0;1;1;0}

Step 2 - Convert Boolean array into row numbers

=INDEX($C$2:$C$6, SMALL(IF(COUNTIF($B$9:$B$10,$B$2:$B$6), ROW($B$2:$C$6)-MIN(ROW($B$2:$C$6))+1), ROW(A1)))

IF(COUNTIF($B$9:$B$10,$B$2:$B$6), ROW($B$2:$C$6)-MIN(ROW($B$2:$C$6))+1)

becomes

IF({1;0;1;1;0}, ROW($B$2:$C$6)-MIN(ROW($B$2:$C$6))+1)

becomes

IF({1;0;1;1;0}, {2;3;4;5;6})-MIN({2;3;4;5;6}))+1)

becomes

IF({1;0;1;1;0}, {2;3;4;5;6})-2+1)

becomes

IF({1;0;1;1;0}, {1;2;3;4;5})

and returns {1;FALSE;3;4;FALSE}.

Step 3 - Returns the k-th smallest row number

=INDEX($C$2:$C$6, SMALL(IF(COUNTIF($B$9:$B$10,$B$2:$B$6), ROW($B$2:$C$6)-MIN(ROW($B$2:$C$6))+1), ROW(A1)))

SMALL(IF(COUNTIF($B$9:$B$10,$B$2:$B$6), ROW($B$2:$C$6)-MIN(ROW($B$2:$C$6))+1), ROW(A1))

becomes

SMALL({1;FALSE;3;4;FALSE}, ROW(A1))

becomes

SMALL({1;FALSE;3;4;FALSE}, 1)

returns 1.

Step 4 - Returns a value of the cell at the intersection of a particular row and column

=INDEX($C$2:$C$6, SMALL(IF(COUNTIF($B$9:$B$10,$B$2:$B$6), ROW($B$2:$C$6)-MIN(ROW($B$2:$C$6))+1), ROW(A1)))

becomes

=INDEX($C$2:$C$6, 1)

becomes

=INDEX({1.5;2;1.5;1.7;3}, 1)

and returns 1.5 in cell B13.

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

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

ROW(reference) Returns the rownumber of a reference

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

Recommended articles

Check out these posts and learn more about vlookup.

Related 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

Lookup values in a range using two or more criteria and return multiple matches in excel, part 2

Lookup a value in a list and return multiple matches in excel

Vlookup with multiple matches returns a different value in excel