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:

copied down as far as needed.

Array formula in B13 without named ranges:

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:

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.

• How to return multiple values using vlookup
• Vlookup with 2 or more lookup criteria and return multiple matches in excel
• Using array formula to look up multiple values in a list
• Search for multiple text strings in multiple cells in excel
• Vlookup – Return multiple unique distinct values in excel
• Fuzzy vlookup (excel array formula)
• Lookup using two criteria in excel
• Search case sensitive and return multiple values in excel
• Vlookup a range in excel
•  Vlookup with multiple matches returns a different value in excel
• Vlookup of three columns to pull a single record