Vlookup with 2 or more lookup criteria and return multiple matches in excel
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:
- Lookup a value in a list and return multiple matches in excel
- Lookup values in a range using two or more criteria and return multiple matches in excel
- How to return multiple values using vlookup in excel
- Lookup with multiple criteria and display multiple search results using excel formula, part 4
- Lookup with multiple criteria and display multiple search results using excel formula
- Lookup with multiple criteria and display multiple search results using excel formula, part 3
- Lookup with multiple criteria and display multiple search results using excel formula, part 2
- Lookup two index columns returning multiple matches in excel
- Vlookup of three columns to pull a single record
- Sum adjacent values using multiple lookup text values in a column in excel



January 3rd, 2010 at 6:50 am
Oscar, on the first formula, any reason why you have multiplied with ))*(SEARCH(search_tbl, TRANSPOSE(INDEX(tbl, , 1, 1))))? As this step looks redunant?
January 3rd, 2010 at 10:27 am
Thanks!
I forgot this post.
Using countif() instead of search() reduces formula size.
The reason why I multiplied two search() in the first place, was to remove any cells that contained the search criteria, I was looking for exact matches.
January 6th, 2010 at 12:28 am
Does anyone know how to do a vlookup of three columns to pull a single record?
January 6th, 2010 at 10:16 am
Andy,
Can you elaborate?
Match a single criterion in any of three columns?
Match three different criteria in each column?
Match any of three different criteria in any column?
January 10th, 2010 at 10:41 pm
Andy,
See this post: http://www.get-digital-help.com/2010/01/10/vlookup-of-three-columns-to-pull-a-single-record/