*The tutorial shows a few ways to Vlookup multiple values in Excel based on one or more conditions and return multiple matches in a column, row or single cell.*

When using Microsoft Excel for data analysis, you may often find yourself in situations when you need to get all matching values for a specific id, name, email address or some other unique identifier. An immediate solution that comes to mind is using the Excel VLOOKUP function, but the problem is that it can only return a single match.

Vlookup for multiple values can be done via a combined use of several functions. If you are far from being an Excel expert, don't hurry to leave this page. I will do my best to explain the underlying logic so that even a novice could understand the formulas and adjust them for solving similar tasks. Even better, I will show you another possible solution that takes just a few mouse clicks and does not require any knowledge of Excel formulas at all!

As mentioned in the beginning of this tutorial, there is no way to make Excel VLOOKUP return multiple values. The task can be accomplished by using the following functions in an array formula:

- IF - evaluates the condition and returns one value if the condition is met, and another value if the condition is not met.
- SMALL - gets the k-th smallest value in the array.
- INDEX - returns an array element based on the row and column numbers you specify.
- ROW - returns the row number.
- COLUMN - returns the column number.
- IFERROR - traps errors.

Below you will find a few examples of such formulas.

Let's say, you have the seller names in column A and the products they sold in column B, column A containing a few occurrences of each seller. Your goal is to get a list of all products sold by a given person. To have it done, please follow these steps:

- Enter a list of unique names in some empty row, in the same or another worksheet. In this example, the names are input in cells D2:G2:

**Tip.**To quickly get all different names in a list, you can use a formula to extract distinct values in Excel. - Under the first name, select a number of empty cells that is equal to or greater than the maximum number of possible matches, enter one of the following array formulas in the formula bar, and press Ctrl + Shift + Enter to complete it (in this case, you will be able to edit the formula only in the entire range where it's entered). Or, you can enter the formula in the first cell, hit Ctrl + Shift + Enter, and then copy the formula to a few more cells down the column (in this case, you will be able to edit the formula in each cell individually).
`=IFERROR(INDEX($B$3:$B$13, SMALL(IF(D$2=$A$3:$A$13, ROW($B$3:$B$13)-2,""), ROW()-2)),"")`

or

`=IFERROR(INDEX($B$3:$B$13,SMALL(IF(D$2=$A$3:$A$13,ROW($A$3:$A$13)- MIN(ROW($A$3:$A$13))+1,""), ROW()-2)),"")`

As you see, the 1

^{st}formula is a bit more compact, but the 2^{nd}one is more universal and requires fewer modifications (we will elaborate more on the syntax and logic a bit further). - Copy the formula to other columns. For this, select the range of cells where you've just entered the formula, and drag the fill handle (a small square at the lower right-hand corner of the selected range) to the right.

The result will look something similar to this:

This is an example of intermediate to advanced uses of Excel that implies basic knowledge of array formulas and Excel functions. Working from the inside out, here's what you do:

**IF function**At the core of the formula, you use the IF function to get the positions of all occurrences of the lookup value in the lookup range: IF(D$2=$A$3:$A$13, ROW($B$3:$B$13)-2,"")

IF compares the lookup value (D2) with each value in the lookup range (A3:A13), and if the match if found, returns the relative position of the row; an empty string ("") otherwise.

The relative positions of the rows are calculated by subtracting 2 from ROW($B$3:$B$13) so that the first row has position 1. If your return range begins in row 2, then subtract 1, and so on. The result of this operation is the array {1;2;3;4;5;6;7;8;9;10;11}, which goes to the value_if_true argument of the IF function.

Instead of the above calculation, you can use this expression: ROW(lookup_column)- MIN(ROW(lookup_column))+1, which returns the same result but does not require any changes regardless of the return column location. In this example, it'd be ROW($A$3:$A$13)- MIN(ROW($A$3:$A$13))+1.

So, at this point you have an array consisting of numbers (positions of matches) and empty strings (non-matches). For cell D3 in this example, we have the following array:

If you check with the source data, you will see that "Adam" (lookup value in D2) appears on the 3

^{rd}, 8^{th}and 10^{th}positions in the lookup range (A3:A13).**SMALL function**

Next, the SMALL(array, k) function steps in to determine which of the matches should be returned in a specific cell.With

*array*already established, let's work out the*k*argument, i.e. the k-th smallest value to be returned. For this, you make a sort of an "incremental counter" ROW()-n, where "n" is the row number of the first formula cell minus 1. In this example, we entered the formula in cells D3:D7, so ROW()-2 returns "1" for cell D3 (row 3 minus 2), "2" for cell D4 (row 4 minus 2), etc.As the result, the SMALL function pulls the 1

^{st}smallest element of the array in cell D3, the 2nd smallest element in cell D4, and so on. And this transforms the initial long and complex formula into a very simple one, like this:**Tip.**To see the calculated value behind a certain part of the formula, select that part in the formula bar and press F9.**INDEX function**This part is easy. You use the INDEX function to return the value of an array element based on its row number.

**IFERROR****function**And finally, you wrap the formula in the IFERROR function to handle possible errors, which are inevitable because you cannot know how many matches will be returned for this or that lookup value, and therefore you copy the formula to a number of cells equal to or greater than the number of possible matches. Not to scare your users with a bundle of errors, simply replace them with an empty string (blank cell).

Putting all this together, we get the following generic formulas to Vlookup multiple values in Excel:

**Formula 1**:

IFERROR(INDEX(*return_range*, SMALL(IF(*lookup_value* = *lookup_range*, ROW(*return_range* )- *m* ,""), ROW() - *n* )),"")

**Formula 2**:

IFERROR(INDEX(*return_range*, SMALL(IF(*lookup_value* = *lookup_range* , ROW(*lookup_range*) - MIN(ROW(*lookup_range* ))+1,""), ROW() - *n*)),"")

Where:

*m*is the row number of the first cell in the return range minus 1.*n*is the row number of the first formula cell minus 1.

In case you want to return multiple values in rows rather than columns, change the above formulas this way:

`=IFERROR(INDEX($B$3:$B$13, SMALL(IF($D3=$A$3:$A$13, ROW($B$3:$B$13)-2,""), COLUMN()-4)),"")`

Or

`=IFERROR(INDEX($B$3:$B$13,SMALL(IF($D3=$A$3:$A$13,ROW($A$3:$A$13)- MIN(ROW($A$3:$A$13))+1,""),COLUMN()-4)), "")`

Like in the previous example, both are array formulas, so remember to press the Ctrl + Shift + Enter shortcut to complete them correctly.

The formulas work with the same logic as in the previous example, except that you use the COLUM function instead of ROW to determine which matching value should be returned in a specific cell: COLUMN()-n. Where n is the column number of the first cell where the formula is entered minus 1. In this example, the formula is input in cells E2:H2. With E being the 5^{th} column, *n* is equal to "4" (5-1=4).

Wrapping up, here are the generic formulas for Vlookup with multiple results returned in rows:

**Formula 1**:

IFERROR(INDEX(*return_range*, SMALL(IF(*lookup_value* = *lookup_range*, ROW(*return_range*) - *m*, ""), COLUMN() - *n*)), "")

**Formula 2**:

IFERROR(INDEX(*return_range*, SMALL(IF(*lookup_value* = *lookup_range*, ROW(*lookup_range*) - MIN(ROW( *lookup_range*))+1,""),COLUMN() - *n*)), "")

Where:

*m*is the row number of the first cell in the return range minus 1.*n*is the column number of the first formula cell minus 1.

You already know how to Vlookup for multiple values in Excel based on one condition. But what if you want to return multiple matches based on two or more criteria? Taking the previous examples further, what if you have an additional *Month* column, and you are looking to get a list of all products sold by a given seller in a specific month?

If you are familiar with arrays formulas, you may remember that they allow using asterisk (*) as the AND operator. So, you can just take the formulas discussed in the two previous examples, and have them check multiple conditions as demonstrated below.

IFERROR(INDEX(*return_range*, SMALL(IF(1=((--(*lookup_value1=lookup_range1*)) * ( --(*lookup_value2*=*lookup_range2*))), ROW(*return_range*)-*m*,""), ROW()-*n*)),"")

Where:

*m*is the row number of the first cell in the return range minus 1.*n*is the row number of the first formula cell minus 1.

Assuming the Seller list (*lookup_range1*) is in A3:A30, the Month list (*lookup_range2*) is in B3:B30, the seller of interest (*lookup_value1*) is in cell E3, and the month of interest (*lookup_value2*) is in cell F3, the formula takes the following shape:

`=IFERROR(INDEX($C$3:$C$30, SMALL(IF(1=((--($E$3=$A$3:$A$30)) * (--($F$3=$B$3:$B$30))), ROW($C$3:$C$30)-2,""), ROW()-2)),"")`

This layout may be useful for creating a dashboard, e.g. your users can enter a name in E3, month in F3 and get a list of products in column G:

If you want to pull multiple values based on multiple criteria sets, you may prefer the horizontal layout where results are returned in rows. In this case, use this following generic formula:

IFERROR(INDEX(*return_range*, SMALL(IF(1 = ((--(*lookup_value1=lookup_range1*)) * (--(*lookup_value2* = *lookup_range2*))), ROW(*return_range*) - *m*, ""), COLUMN() - *n*)),"")

Where:

*m*is the row number of the first cell in the return range minus 1.*n*is the row number of the first formula cell minus 1.

For our sample dataset, the formula goes as follows:

`=IFERROR(INDEX($C$3:$C$30, SMALL(IF(1=((--($E3=$A$3:$A$30)) * (--($F3=$B$3:$B$30))), ROW($C$3:$C$30)-2,""), COLUMN()-6)),"")`

And the result can resemble this:

In a similar manner, you can do multiple Vlookup with three, four or more conditions.

Basically, the formulas to Vlookup multiple values with multiple conditions work with the already familiar logic, explained in the very first example. The only difference is that the IF function now tests multiple conditions:

1=((--(*lookup_value1*=*lookup_range1*))*(--(*lookup_value2*=*lookup_range2*))*…)

The result of each lookup_value=lookup_range comparison is an array of logical values TRUE (condition is met) and FALSE (condition is not met). The double unary operator (--) coerces the logical values into 1's and 0's. And because multiplying by zero always gives zero, in the resulting array, you have 1 only for those elements that meet all of the specified conditions. Now, you simply compare the final array with number 1 so that the ROW function returns the numbers of rows that meet all the conditions, an empty string otherwise.

**A word of caution**. All of the multiple Vlookup formulas discussed in this tutorial are array formulas. As such, each formula iterates through all elements of the arrays every time the source data is changed or the worksheet is recalculated. On large worksheets containing hundreds or thousands of rows, this may significantly slow down your Excel.

This is how you Vlookup multiple matches in Excel using formulas. To have a closer look at the examples and probably reverse-engineer the formulas for better understanding, you are welcome to download the Excel Vlookup Multiple Values sample worksheet.

I will be upfront - I don't know a formula to Vlookup duplicates that would output multiple matches in a single sell. However, I do know a formula-free (read "stress-free" :) way to do this, by using two add-ins included with our Ultimate Suite for Excel. If you don't have it in your Excel yet, you can download a free 14-day trial version here, and then follow the steps outlined below.

As shown in the screenshot, we continue working with the dataset we've used in the previous example. But this time we want to achieve something different - instead of extracting multiple matches in separate cells, we want them to appear in a single sell, separated with a comma, space, or some other delimiter of your choosing.

In your main table, enter a list of unique names in the first column, months in the second column, and arrange them like shown in the screenshot below. After that, carry out the following steps:

- Select your main table or click any cell within it, and then click the
**Merge Two Tables**button on the ribbon:

- The add-in is smart enough to identify and pick the entire table, so you just click
*Next*:**Tip.**When using the tool for the first time, it stands to reason to select the**Create a backup copy of the worksheet**box in case something goes wrong. - Select the lookup table, and click
*Next*.

- Choose one or more matching pairs of columns that should be compared in the main table and lookup table (in this example, it's the
*Seller*and*Month*columns), and then click*Next*.

- Select the column(s) from which you want to pull matching values (
*Product*in this example), and click*Next*.

- Tell the add-in how exactly you want multiple matches to be arranged in the main table. For this example, we need the following option:
**Insert rows with duplicate matching values after the row with the same value**. Make sure that no other option is selected and click*Finish*.

At this point, you will have the following result - all matching rows are pulled to the main table and grouped by the values in the lookup columns - first by Seller, and then by Month:

The resulting table already looks nice, but it's not exactly what we wanted, right? As you remember, we are looking to Vlookup multiple matches and have them returned in a single sell, comma or otherwise separated.

To merge "duplicate rows" in a single row, we are going to use another tool - Combine Rows Wizard.

- Select the table produced by the Merge Tables tool (please see the screenshot above) or any cell within the table, and click the
**Combine Rows**button on the ribbon:

- Check if the add-in's got the table right, and click
*Next*:

- Select the key column or columns (
*Seller*and*Month*in this example), and click*Next*:

- Select the column(s) that contains multiple matches (
*Product*in this example), choose the desired delimiter (semicolon, comma, space or line break), and click*Finish*.Optionally, you can enable one of the additional features, or both:

*Delete duplicate values*- if the column to be merged contains a few identical values, the first occurrence will be kept, duplicate matches will be deleted.In this example, we do not check this option, and the add-in returns all found matches. For example, in cell C2, we have this string:

*Lemons, Bananas, Apples, Lemons, Bananas*(please see the result on step 5 below). If you choose to delete duplicates, the result would be:*Lemons, Bananas, Apples*.*Skip empty cells*- self-explanatory :)

- Allow the add-in a few seconds for processing, and you are all set!

This is how you can look up and return multiple values in Excel using our tools. If you are curious to give them a try, a fully-functional evaluation version of the Ultimate Suite can be downloaded here. If the add-ins prove useful in your work, be sure to grab the 15% off coupon code that we provide exclusively to our blog readers: AB14-BlogSpo.

I thank you for reading and hope to see you on our blog next week!

Excel formulas
Excel functions
Vlookup in Excel
Merge data in Excel
SumIf
Excel CountIf
Excel Compare
Excel If statement
Excel Charts
Pivot Table
Updates
Excel conditional formatting
Excel formatting
Excel time
Excel duplicates
Excel date
Excel tips
Excel macro
Outlook
Google Sheets
Outlook duplicates
Outlook templates

## 166 Responses to "How to Vlookup to return multiple matches in Excel"

Good day !

Using the row array formula =IFERROR(INDEX($B$3:$B$13,SMALL(IF($D3=$A$3:$A$13,ROW($A$3:$A$13)- MIN(ROW($A$3:$A$13))+1,""),COLUMN()-4)), ""), let's say that Adam's first line column B is blank rather than "Bananas" - how can I skip the blank and show the next real value in cell E1? (in other words, E1 would be "Oranges")

Thanks!

Hi

I need some help to do retrieve information from an excel sheet and populate specific columns in another excel sheet within the same workbook. I tried this but I have given up with the formula to get it working.

For examle,

1. Sheet 1 is the master sheet where I enter the information into each cell.

For example,

Device ID Device MAC Hostname Service Tag

FS-001 Z9264F-ON 20040F0E3B4E frxsw-ds01 H5WMXC2

I need help to make a formula to retrieve information from a 'master' sheet (where information is entered manually) and populate specific cells in a 'target' sheet to avoid re-entering this information.

For example,

Master sheet

-----------

A B C D

1 Device ID Device MAC Hostname Service Tag

2 FS-001 00:00:00:00:00:00 device01 12345

Target Sheet

----------

A B C D E

1 Device ID Hostname IP address Device MAC Gateway

2 FS-001 device01 00:00:00:00:00:00

So, if on the 'Target' sheet A2 = 'Master' sheet A2's value of FS-001, then values from Master sheet B2 and C2 will be populated

in the cells of 'Target' sheet D2 and B2 respectively.

I need help to get Max value from set value but if any data is 0 ( zero) then return result should be 0(zero). Pls see below data & result what I want. Pls advise.

A 20

B 20

C 30

A 40

B 10

D 60

B 35

D 40

A 0

D 0

A 40

Result should be

A 0

B 35

C 30

D 0

This should do it. Assuming your data begins in cell A1. Make sure you hit Ctrl+Shift+Enter when finished as this is an array formula. {=IF(MIN(IF("A"=$A$1:$A$11,$B$1:$B$11,""))=0,0,MAX(IF("A"=$A$1:$A$11,$B$1:$B$11,"")))}

=VLOOKUP(E5,A1:B8,2,0)&", "&INDEX(B1:B8,MATCH(E5,A1:A8,0)+2)

Dear Svetlana

Your pages on Excel are really incredible! I learned a lot, but understand that there is still so much to learn!

I am searching for a solution to retrieve data from an input sheet. When one selects one or more search terms in another sheet (from a pre-defined list) I would like to see all the results that meet the conditions. It is very similar to your examples here, "Vlookup multiple matches based on multiple conditions". But instead of AND I would need OR. Eg: if the search term is 'apples', I would like to see Excel giving me a list of all the apple sales. If the search terms are 'apples' and 'bananas', I would like to see Excel giving me a list of all the sales of apples and bananas. I am searching for hours ans hours, but cannot manage it... Maybe you have the solution?

Thanks in advance!

Hi

I need some help with retrieving value from a table:

min max 1 2 3 4 5 6 7 8 9 10

0 8 0.00% 0.00% 2.50% 4.00% 5.00% 6.00% 7.00% 8.00% 9.00% 10.00%

9 10 5.00% 5.00% 7.50% 9% 10% 11% 12% 13% 14% 15%

11 24 7.50% 7.50% 10% 11.50% 12.50% 13.50% 14.50% 15.50% 16.50% 17.50%

25 49 10.00% 10.00% 12.50% 14% 15% 16% 17% 18% 19% 20%

50 99 12.50% 12.50% 15% 16.50% 17.50% 18.50% 19.50% 20.50% 21.50% 22.50%

100 199 15.00% 15.00% 17.50% 19% 20% 21% 22% 23% 24% 25%

200 >200 17.50% 17.50% 20% 21.50% 22.50% 23.50% 24.50% 25.50% 26.50% 27.50%

I am using the formula: =INDEX($E$7:$N$13,MATCH(C3,D7:D13,2), MATCH(C10,Sheet1!E6:N6,0))

However, everytime it is reading only the first line

Can you help me out. Thanks for reading.

Your information was very helpful, thank you!!! I want to use the formulas to do an "or" statement. I am using the formula {=IFERROR(INDEX('CTE Detail'!$F$2:$F$900000,SMALL(IF('CTE Detail'!$M$2:$M$900000=auto!$F$1,ROW('CTE Detail'!$F$2:$F$900000)-1,""),ROW()-8)),"")}. This works as long as each program only has one subject code. I am bringing 2 spreadsheets together. What I am looking for is all of the students in certain classes by subject code (auto!$F$1). I have some programs that have multiple subject codes, how do I write the formula? I believe the * is for an "and" statement. Thank you,

I need to bring data from a master sheet where there is more than one entry, but might have slightly different data in a number of columns.

i need help

Rujan Shakya Absent

Abhishekh Wosti Present

Santosh Adhikari Present

Kshitiz Pokhrel Present

Rujan Shakya Present

in above details need to just take present name to another sheet

can anyone please help me

Hello - I tried to implement Formula 1 in this article, Vlookup multiple values and return results in a column, using the same data entered in the same cells in an Excel 2019 spreadsheet. But the formula doesn't work. To see why, I stripped out the IFERROR code and just ran the INDEX function without error checking. This showed that the formula produces a #Value error in the result. Does anyone know why the example is not working for me? I'm truly stumped.

Hi Larry,

It's difficult to say what the problem might be without seeing your data and formula. IFERROR is a very simple function and usually it does not cause any issues.

You can download our sample workbook with working formulas and copy/past the desired formula from there to try it on your data.

This was so incredibly helpful! I looked at other tutorials prior to this, and none of them gave the background on "why" or "what" each step did so I couldn't manipulate the other examples to fit my needs. This was the only truly comprehensive guide I came across. Thank you so much!

THANKS FOR YOUR HELP!!

doing similar to Formula 1. Vlookup multiple values and return results in a column

However I have the data in a separate sheet called 'Eg Data' hence I changed my formula to read as follows:

=IFERROR(INDEX('Eg Data'!$B$3:$B$13,SMALL(IF(D$5='Eg Data'!$A$3:$A$13,ROW('Eg Data'!$A$3:$A$13)- MIN(ROW('Eg Data'!$A$3:$A$13))+1,""), ROW()-2)),"")

In example data all is the same as per example above in 'Formula 1. Vlookup multiple values and return results in a column' and using same cells (a3:b13)

the other only difference is that the name in my case 'Adam' is not in cell D2 as per example above but in D5 and I want the results in D6 no in D3 as per example above.

This seems to give a problem when I moved the name from D2 to D5 and results to be in D6 instead of D3

Does the formula need changing?

This was a great tutorial, thank you very much! I got the formulas to work for my database, however I have an additional restriction. Bringing it to this example (I'm doing results in a ROW), let's say the products are listed as ORANGES - APRICOTS - BANANAS - APPLES - LEMONS. So if Adam sold Bananas, Oranges and Lemons, I want his results to show ORANGES - *blank* - BANANAS - *blank* - LEMONS. To make it easier to understand, my categories are time, so I need the results chronologically (6-10-12-14 hours). So if a result appears in 12 and 14, I want to see the 4 columns as blank-blank-result-result, but what I'm getting is result-result-blank-blank. Any thoughts?

Sally Oranges

Robert Apricots

Adam Bananas

Tom Apples

Robert Lemons

Tom Apricots

Sally Bananas

Adam Oranges

Tom Bananas

Adam Lemons

Sally Apples

X y

Please suggest what should I do if my range is extended till x & y. Simply changing range in your formula is not working.

Please give me the result column c, as completed or incompleted when lookup from column a into column b.

Column (a) Column (b) Result Column (c)

OTE3‐L1‐IM‐300992‐01 12-Apr Completed

OTE3‐L1‐IM‐300992‐01 13-Apr Completed

OTE3‐L1‐IM‐300992‐02 13-Apr Completed

OTE3‐L1‐IM‐300992‐02 Incompleted

OTE3‐L1‐IM‐300992‐03 Incompleted

OTE3‐L1‐IM‐300992‐03 Incompleted

OTE3‐L1‐IM‐300992‐03 Incompleted

OTE3‐L1‐IM‐300992‐03 Incompleted

Thanks a lot for your effort you saved my time.

Very helpful. Thank You!!!

HI

When i use this code the excel have a error

=IFERROR(INDEX($B$3:$B$13, SMALL(IF(D$2=$A$3:$A$13, ROW($B$3:$B$13)-2,""), ROW()-2)),"")

the core of error begins from==>

IF(D$2=$A$3:$A$13, ROW($B$3:$B$13)-2,"")

i dont know what should i do :-(

thank you

i fund the issue

the problem was in

ROW()

cause of copy pasting the formula it reffer to the cell which is copied and it is not the rank that we want. so should change to 1,2,3 or "ROW()-cell row number+1"

You're the best! This saved me so many headaches, thank you so much!!

I stumbled upon this formula and spent about 2 hours trying to get it to work, but I finally did after seeing an error I was making in the comments (our fault for not reading fully)

My question is, using the very top example with sellers and produce, I manually need to change my cell reference from $D2 to $D3. My spread sheet contains over two hundred sellers and about a hundred different types of produce. So in a separate column I have my sellers, Adam being D2 and Robert D3. I can drag the formula for Adam all 100 rows.

But if I stop at row 25, and switch to D3 for Robert, I cannot copy them down the same way. I have to modify $D3 and ALSO the row to be -24 (when it was originally -2).

Is there a way around this? I'd rather only have to change one aspect instead of two if possible because there are so many. I can't set a predetermined spacing because it changes daily.

Thanks!

Side note, the formula I was using was this one below:

=IFERROR(INDEX($B$3:$B$13,SMALL(IF(D$2=$A$3:$A$13,ROW($A$3:$A$13)- MIN(ROW($A$3:$A$13))+1,""), ROW()-2)),"")

Goal: I would like to vlookup or index match an ID from another workbook using a vba button that then returns the ID name, City, Region, etc. My goal is that I have a button I search the ID # and it returns the criteria that I want that is located in another worksheet. I know to start it with the Workbooks.Open more focused on how to do the index match or vlookup to return the criteria from that sheet. Thanks

The one about Vlookup of multiple values is an absolute beauty. I have needed this function desperately and it worked exactly the way I have needed it. I don't know how to thank you. Still Thanks a million.

IF YOURS WORKS FOR THE FIRST CELL BUT NOT THE REST, CLICK ON THE FORMULA BAR OF THE FIRST CELL AND CLICK - CTR SHFT ENTER - THEN DRAG THAT FORMULA. THE FORMULA SHOULD NOW HAVE {funky brackets}.