## 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:

**2**) + CTRL + SHIFT + ENTER

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

### 36 Responses to “Vlookup with 2 or more lookup criteria and return multiple matches in excel”

### Leave a Reply

**How to add vba code to your comment:**

[vb 1="vbnet" language=","]

your code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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?

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.

Does anyone know how to do a vlookup of three columns to pull a single record?

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?

Andy,

See this post: http://www.get-digital-help.com/2010/01/10/vlookup-of-three-columns-to-pull-a-single-record/

Hello Oscar,

I tried the formulas couple times and they do not work. I want Vlookup to add all the group A $ and placed on Sheet2 group A. For example:

Sheet1:

GRP $

A 2

A 2

A 2

B 3

B 3

B 3

C 4

C 4

C 4

Sheet2:

Grp $

A ???

B ???

C ???

if I add manually group A total is $6 and B total is $9 and C total is $12.

Thank you very much,

newsuteuser2011

newsiteuser2011,

or create a pivot table on sheet2

Oscar, what happen if I need to use IF(ISERROR(Vlookup)) and the datas are pretty big for every month. Is there a way to request Vlookup to pickup more than the first data?

Thank you,

Newsiteuser2011

newsiteuser2011,

Try the formula in this post: How to return multiple values using vlookup in excel

I have a large database with 40 columns, I wish to extract a few column data into separate sheet for printing. The goal is actually a male name list and a female name list with another criteria from a column that is confirmed. Which page shows the examples?

Thank you

Alex Chiok

Alex Chiok,

Excel tables are easy and fast!

http://office.microsoft.com/en-us/excel-help/overview-of-excel-tables-HA010048546.aspx

Oscar,

I am using this formula and have found that it does not produce all of the results from the data file.

My data sheet contains:

Column (A) - Account Number

Column (B) - Date

Column (C) - Notes

There are 1 - 15 instances of the account number in Column (A).

The instances may be on different or the same day (date) in column (B).

The notes in Column (C) are all different.

The results being returned are limited to only two rows per date.

So if I have 5 Notes on 01/01/2012....the results are only returning 2 of them...it appears to be the two middle rows.

Can you help.

Thanks for the great formula!!

I should have included the formula mentioned above just to be clear:

=INDEX(COMMENTS!$I$2:$I$15000, SMALL(IF((GET_COMMENTS!$E$3=COMMENTS!$A$2:$A$15000)*(GET_COMMENTS!$E$3=COMMENTS!$A$2:$A$15000), ROW(COMMENTS!$A$2:$A$15000)-MIN(ROW(COMMENTS!$A$2:$A$15000))+1, ""), ROW(A1)))

Oscar,

Nevermind, I'm loosing it. Too many hours in front of a spreadsheet. :)

beth,

Download file:

beth.xlsx

Thanks, This is so useful!!

your explanation makes wonders happen.

But can I ask how do you learn this kind of stuff?

Richard,

Thanks!!

But can I ask how do you learn this kind of stuff?Inspired by others. Trial and error. As far as I know there are no array formula books out there.

Hi Oscar,

On the above example, is there any way I can tranpose the results against the original search ref. as oppose to the vertical lists so that I don't have just a long string of values. That way I can quickly match the corresponding values with the original search ref.

Hope that makes sense!

Emma,

I believe this post is what you are looking for: http://www.get-digital-help.com/2009/10/25/how-to-return-multiple-values-using-vlookup-in-excel/#horizont

Thanks for the references to excel on your website, they have been very helpful. I am trying to alter your formula without any avail.

I am trying to perform a lookup with two criteria and return multiple values which matches the search. I have two worksheets on named Source Data and Available Filler by Page. Source Data has four columns: Book #, Filler Name, Page #, Size

I neeed to lookup the book # and corresponding size to return the page numbers which are available for them horizontally, but i have only been able to return only one criteria because I am not using the AND function correctly inside the IF statement. Here is the formula I have been using:

=IFERROR(INDEX('Source Data'!$C$2:$C$10000,SMALL(IF(AND('Available Fillers by Page'!$B$7='Source Data'!$A$2:$A$10000,'Source Data'!$D$2:$D$10000='Available Fillers by Page'!$E$8),ROW('Source Data'!$A$2:$A$10000)-MIN(ROW('Source Data'!$A$2:$A$10000))+1,""),COLUMN(A1))),"")

Any assistance would be great. Thank you in advance.

I found a formula on another page but how do I get the formula to list the values horizontally. Here is the new formula I used:

=IFERROR(INDEX('Source Data'!$C$2:$C$10000, SMALL(IF(('Available Fillers by Page'!$B$7='Source Data'!$A$2:$A$10000)*('Available Fillers by Page'!$E8='Source Data'!$D$2:$D$10000), ROW('Source Data'!$C$2:$C$10000)-MIN(ROW('Source Data'!$C$2:$C$10000))+1), COLUMN(1:1))),"NFA")

This formula only lists the first value when copied horizontally.

Nevermind my last post, I got it. Once again thank you for the website. I will leep it as a favorite because I know I will be needing it another time.

Dominica Smith,

I am happy you found what you were looking for.

Hi Oscar,

Thanks for the example. i am not sure how to modify it so it meets my need.

Going back to the example, i want to write a formula to display "2" if "pen" AND "$1,50" are the criteria because they exists 2 times...

Hope this clarify. Thanks in advance.

Cheers,

Celic

Celic,

Formula:

=COUNTIFS(B2:B6,B9,C2:C6,C9)

Hi sir I need your help I have spread sheet in coloum A is tel# B is name C is eye glass rx ,.....etc this for the customer report and one customer bought the glass many times always he is using the same tel# each time he had diffrent priscription this all the result is in sheet#1 in sheet # 2 I want find the his file report according to tel# I am useing the vlookup formula to find the report when I use the vlook up I got the first report only how can I find if customer purchase 10 times one by one result with the same tel# my english is not really good thanks for your time Mehaboob mehaboobi@gmail.com

mehaboob

Read this post:

http://www.get-digital-help.com/2009/10/25/how-to-return-multiple-values-using-vlookup-in-excel/#multiple

Hi Oscar,

i am hoping you could please assist me with the following, i have looked through many of your posts but cannot seem to find a solution.

I am trying to match 2 cell values, and return a third value from a source sheet in vertical rows.

the formula i am trying is:

=INDEX('2014'!B3:B16,SMALL(IF($D$2='2014'!F3:F16)*('Feb 14'!E2='2014'!A3:A16),MATCH(ROW('2014'!B3:B16),ROW('2014'!B3:B16))),ROW(A1))) +ctrl + shift +enter

where '2014' is the source sheet

'2014'!B3:B16 is the column with the values i want to return (name)

$D$2 is the city name ='2014'!F3:F16 is the location column

$E$2 is the month name ='2014'!A3:A16 is the date column

I have tried other formulas too but nothing seems to be working, this seems to be the closest to what i am after.

If you could help it would be greatly appreciated.

Thanks.

Holly, you have forgotten absolute cell references.

=INDEX('2014'!$B$3:$B$16, SMALL(IF($D$2='2014'!$F$3:$F$16)*('Feb 14'!$E$2='2014'!$A$3:$A$16), MATCH(ROW('2014'!$B$3:$B$16), ROW('2014'!$B$3:$B$16))), ROW(A1)))

I am not sure about these cell references: $D$2 and $E$2. It depends on what you are doing.

Oscar this is a great post thanks! I have a question similar to the original post.

If the stores names that these items were purchased at were in cells A2:A6 for example- Walmart in A2, Target in A3, CVS in A4, A5 and A6. Also assume that in cell A7 was Walmart, B7 was Pen, and C7 was $3,25

I need a formula to say if the pen was purchased at Walmart how much did it cost? Instead of search criteria being Pen and Paper I would like the search criteria to be Walmart and Pen. The return would be "$1,50" and "$3,75"

Thanks in advance!

correction: I had one typo the return would be "$1,50" and "$3,25"

John,

Array formula in cell B14:

=INDEX($C$2:$C$7, SMALL(IF(($B$10=$A$2:$A$7)*($B$2:$B$7=$C$10), MATCH(ROW($C$2:$C$7), ROW($C$2:$C$7)), ""), ROW(A1)))

Hi, I need some help!

I am trying to create a way to search different kits at one time. Let's say I have three columns, one for "Kits", one for "Products", and one for "Quantities". Is there a way that I can search multiple kits at one time to bring up the different products and quantities? My data spreadsheet has several data points with over 3,000. What is the easiest way to go about this? Thanks!

Can you please e-mail me so I can attach the file that I need to have these search entries for? Every time I try creating or editing the spreadsheet I get a "#VALUE" entry instead of pulling from my data.

Oscar, I hope you can help me. I need to do this formula (preferably with the dynamic range name like you have here)but without the use of the COUNTIF? Ive tried combinations of SUM (IFs) but can not figure it out. The reason is with Countif, sumif and etc you need the source file open... I need to do the multiple lookups when the source file of the table is closed. Is this possible?

=INDEX(tbl, SMALL(IF(COUNTIF(search_tbl, INDEX(tbl, , 1, 1))>0, ROW(tbl)-MIN(ROW(tbl))+1), ROW(A1)), 2)