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

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

Smallest greater than condition

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

Smallest greater than condition

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

How to use the COUNTIF function

#### 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 across multiple sheets

This article demonstrates an array formula that searches two tables on two different sheets and returns multiple results. Sheet1 contains […]

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

I will in this article demonstrate how to use a value from a drop-down list and use it to do […]

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 […]

Vlookup a cell range and return multiple values

VLOOKUP a multi-column range and return multiple values.

Use VLOOKUP and return multiple values sorted from A to Z

The array formula in column G filters values in column C using a condition in cell E3, comparing it with […]

Lookup multiple values in different columns and return multiple values

Jason C asks: I have a set of data, like the one you used in the original example that also […]

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

Paste image link to your comment.

**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/

Hi Oscar,

Thank you for this formula. This is great! I’m trying to do something similar but can’t seem to make it work. I’m hoping you can help me figure it out. I’m trying to compare column A on 2 sheets (Sheet1 and Sheet2) and if it finds a match then compare column C ($ amount) of that row on each sheets and display the difference of column C in column D.

If column A has an ID # with contains letters, numbers, and symbols but isn't always a unique ID # and column C has a $ amount, find the match and put the $ amount difference in column D.

So if the ID # and $ amount match exactly, the difference would be zero, if the ID # matches but the $ amount doesn’t, the difference would be the $ amount of one sheet less the $ amount on the other sheet, and if there is no match the $ difference would be the full $ amount.

Is there any way to use your formula to do this?

Thanks in advance for your help!

Hi, I've been struggling with something for a few days now and I'm not even sure that I can do it in Excel? I've tried various forms of INDEX MATCH and MMULT and anything else that I thought might work. The concept is simple. I need containers in various cabinets for a home remodel. The catalog that I am perusing has TONS of options and I have specific dimensions that I need to satisfy for each cabinet, drawer, etc. So, I have a spreadsheet that lists my actual dimensions in three columns (width, depth, height) and all of the possible options from the catalog along with columns for their dimensions (width, depth, height). I need to find which catalog trashcan would best fit my space. I need to find a dimension for width that matches MY space's width (less than or equal to), same for the depth and height. BUT, I need to do this for all three dimensions AND get Excel to return the product number that best fits. Am I asking too much from Excel? I realize that a database may serve this purpose better but I'm dragging my feet on going with a db.

actual measurements catalog

A B C D E F G

1 width depth height product# width depth height

2 241.3 457.2 393.7 311 111 230 343

3 312 143 211 275

4 313 211 423 275

5 314 230 305 310

6 315 255 400 413

So, product #311 works but it is not the best choice (too small). #315 works for depth but not for width or height. If I were looking for the correct one, I might pick #313 as the best fit but #314 also works. I'd be thrilled if Excel would pick either or both of those LOL. Obviously, my spreadsheet is much larger with too many options for me to look through myself. If I could get excel to return ALL products that fit the criteria (all product dimensions are smaller than the actual space dimensions), I'd be happy.

Any advice?

Sir,

I have a problem,

I have to find out the time of occurance(in Column D) according to Rate(in Column B)

(Rate is in multiple times)

and according to Quantity (in Column C)

(quantity is also repeating )