## Vlookup with 2 or more lookup criteria and return multiple matches

*Article last updated on February 14, 2018*

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

The picture below shows you a table in column B and C, the search criteria is in column B and the results are in column G.

I am not using VLOOKUP at all in this array formula, the 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 VLOOKUP function is not designed to look for multiple values and return multiple values.

**Array formula in E7:**

Recommended article

Question: Here is the problem: i have a data table with 2 columns: A B 2.93 12.8 2.94 12.2 3 […]

### How to enter an array formula

- Copy the aray formula above (Ctrl + c)
- Double click cell G3
- Paste (Ctrl + v)
- Press and hold Ctrl + Shift simultaneously
- Press Enter
- Release all keys

If you made the above steps correctly the formula now has a beginning and ending curly bracket, like this:

{=array_formula)}

Don't enter these characters yourself, they appear automatically.

Array formulas allows you to do advanced calculations not possible with regular formulas.

### How to copy formula to cells below

- Select cell E7
- Copy cell (Ctrl + c)
- Select cell range E8:E9
- Paste (Ctrl + v)

### How the array formula works in cell E7

You can easily follow a long as I explain the array formula, download the workbook. Select cell B13, go to tab "Formulas". Click on "Evaluate formula" button.

Click on "Evaluate" button show above to move to next step.

#### Step 1 - Count matching search criteria in column B

COUNTIF($E$3:$E$4, $B$3:$B$7)

becomes

COUNTIF({"US";"Asia"},{"US";"Europe";"US";"Europe";"Asia"})

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

How to use the COUNTIF function

Counts the number of cells that meet a specific condition.

#### Step 2 - Convert boolean array into corresponding row numbers

IF(COUNTIF($E$3:$E$4, $B$3:$B$7), MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)), "")

becomes

IF({1;0;1;0;1}, MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)), "")

becomes

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

and returns {1;"";3;"";5}

These are the row numbers that correspond to the matching values US and Asia in column B.

Checks if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

#### Step 3 - Returns the k-th smallest row number

SMALL(IF(COUNTIF($E$3:$E$4,$B$3:$B$7), MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)), ""),ROWS($A$1:A1))

becomes

SMALL({1;"";3;"";5}, ROWS($A$1:A1))

becomes

SMALL({1;"";3;"";5}, 1)

and returns 1.

The SMALL function lets you extract a number in a cell range based on how small it is compared to the other numbers in the group.

#### Step 4 - Return a value based on coordinate

INDEX($C$3:$C$7, SMALL(IF(COUNTIF($E$3:$E$4, $B$3:$B$7), MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)), ""), ROWS($A$1:A1)))

becomes

INDEX($C$3:$C$7, 1)

becomes

INDEX({"Pen";"Eraser";"Paper";"Pen";"Paper clip"}, 1)

and returns Pen in cell E7.

Gets a value in a specific cell range based on a row and column number.

### Download excel *.xlsx file

Vlookup with multiple search conditions and return multiple matches.xlsx

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Vlookup – Return multiple unique distinct values

Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]

Vlookup across multiple sheets

This blog post describes how to search two tables on two sheets and return multiple results. Sheet1 contains table1 and […]

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

In this post we are going to extract multiple text values. We are looking for names and the criteria are […]

Lookup and return multiple values on the same row from a range excluding blanks

Jim asks: I downloaded the file lookup-vba3. I think I can use this to help me populate a calendar.I substituted […]

Use a drop down list to search and return multiple values

Ainslie asks: I have multiple worksheets in an excel book. I have a drop down menu on the worksheet entitles […]

Lookup multiple values in a range and return multiple corresponding values

In a previous blog post Extract cell values in a range using a criterion in excel I provided a formula […]

Search values distributed horizontally and return corresponding value

Question: Hi, The formula here works great but I can't figure out how to change it to work with data […]

Vlookup with multiple matches returns a different value

Linda asks in this post: How to return multiple values using vlookup in excel I tried using the formula above […]

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

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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: https://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!

https://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: https://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:

https://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)

I am working in a railway project as planner. How can i create a dynamic strip chart in excel?

Assume total length be 10 kilometers and each cell of 100 meters.

If I update the progress from 9.01 kms to 9.08 kms in the table, how the cell gets highlighted corresponding to the entered chainages.

Sankalp,

Read this: https://www.get-digital-help.com/2017/07/06/track-progress/

How do I lookup lot ref no ranges in between in below data set. For eg lookup for "SEC48" would be "Lot1"

MIN MAX Lot Ref No

SEC1 SEC50 Lot1

SEC51 SEC101 Lot2

SEC102 SEC152 Lot3

SEC153 SEC203 Lot4

SEC204 SEC254 Lot5

SEC255 SEC305 Lot6

Maulik

Thanks for a great comment.

Read this article: https://www.get-digital-help.com/2018/02/26/match-a-range-value-containing-both-text-and-numerical-characters/