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

## How to do multiple Vlookup in Excel using a formula

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.

### Formula 1. Vlookup multiple values and return results in a column

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:

#### How this formula works

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

**Note.**Please notice the proper use of absolute and relative cell references in the formula. All references are fixed except for the relative column reference in the lookup value (D$2), which should change when the formula is copied to other columns to return matches for other lookup values.

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

**Formula 1**:

*return_range*, SMALL(IF(

*lookup_value*=

*lookup_range*, ROW(

*return_range*)-

*m*,""), ROW() -

*n*)),"")

**Formula 2**:

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

**Note.**In the above example, both

*n*and

*m*are equal to "2" because our return range and formula range both begin in row 3. In your worksheets, these may be different numbers.

### Formula 2. Vlookup multiple matches and return results in a row

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

**Note.**For the formula to get copied correctly to other rows, mind the lookup value references, absolute column and relative row, like $D3.

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

**Formula 1**:

*return_range*, SMALL(IF(

*lookup_value*=

*lookup_range*, ROW(

*return_range*) -

*m*, ""), COLUMN() -

*n*)), "")

**Formula 2**:

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

### Formula 3. Vlookup multiple matches based on multiple conditions

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.

#### Return multiple matches in a column

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

#### Return multiple results in a row

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:

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

#### How these formulas work

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.

## How to Vlookup to return multiple values in one cell

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.

#### Source data and expected result

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.

#### Pull rows with multiple matches to the main table

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

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.

#### Combine duplicates rows into one row

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 done!

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!

Awesome:-), Svetlana. Carry on.

Great info!! Another combination I like to use for pulling info in based on a common ID across the two sets is: = INDEX( , MATCH( , ,0))

god gives you every thing you wish

Great ideas,I'd been trying since 2 years to build a formula for same conditions..thanks a lot.

I already copy this formula "=IFERROR(INDEX($B$3:$B$13, SMALL(IF(D$2=$A$3:$A$13, ROW($B$3:$B$13)-2,""), ROW()-2)),"")", but it didn't work. Can you help me?

Hello Nawanto,

Did you adjust the formula for your data? Please read carefully the "How this formula works" section to understand the formula's logic and how to customize it for your own data.

I've tried the exact same data set however, the formula itself doesn't work.

Hi Alex,

You can download the sample worksheet here and make sure the formula does work.

thanks a lot

very best activity to teach vlookup.

regards

Hi. This looks to be a source of great help. I am looking for a solution of my below mentioned problem;

I have a Table 1 in which there are two inputs. 1) Cable Diameter (mentioned in a Column) 2) Max. length per Drum Type (mentioned in different columns in front of respective row of the diameter). I need to fetch the drum type for combination of my inputs of Cable diameter and Max Length.

I hope I am clear in my description of the issue.

Thanks & Regards,

Salman

Awesome, incredibly fantastic

Excel makes life easy

Dept. dr.name opdno. Opdno1

Gastro A. 2.

Gastro B. 5.

Gastro total. 7.

Ent. D. 8

How index match will be used in column opd no1 in row Ent so that 8 will be fetched 4th column 5th row.

Very Informative, Great learning. Thank you :)

Hi I need some help

I have two sheets, one is where the data is being pulled from and the other is where the data is being manipulated. In my data set I have employee numbers that have job titles along with the store numbers.

In the sheet where my data is being manipulated I would like whenever to display all the employees that belong to a particular store and their job titles please help

Hi, Oscar,

to return different pieces of info, you will need to create two columns: one for the employees' numbers and another for their job titles. And each column will require its own formula. Then, use the 1st VLOOKUP formula of this article to return results in a column.

Hello, I need some help...

I tried the formula and ajusted it to my data, and it worked for the cell in which I wrote the formula. However, when I try to drag the formula it doesn't work.

Hello, Mafer,

you're probably experiencing problems because of the cell references. Read this article, to learn more about absolute and relative cell references.

If this doesn't help, send us an example of the workbook with your source data and the result you expect to get to support@ablebits.com.

Hi Svetlana,

I try the formula "=IFERROR(INDEX($B$3:$B$13, SMALL(IF(D$2=$A$3:$A$13, ROW($B$3:$B$13)-2,""), ROW()-2)),"")" but didn't work it brings back "#VALUE!, I copied the exact data from the sample same cell positions but didn't work, could you please help me?

Hi.

Is it possiple to do the lookup with multiple values in a collum from an external file?

Thanks, u solved my big problem of my work in office

Hi i was woundering if anybody could help. i have read the above and many more treads i have a formula that works, i just dont now how to expand the array arcoss muilple colums.

The formula that works =INDEX(B2:B40,(MATCH(E1,J2:J46,0)))

something i dont think is possible but i very much need to work.

=INDEX(B2:B40,(MATCH(E1,J2:U46,0)))

have tried muilple varations creating a helper colunm and concatenating J2 to U2 into one colunm but even that dosnt work

can anybody help

cheers

match does not work for multiple columns.

Hi,

How can you modify the Formula 1 for me if the products names are repeating

Thanks

Svetlana,

This is a great tool, thank you for the wonderful instructions. I've been trying for a while now to do a partial vlookup using these formula's but have had no luck. Do you know why the following doesn't work? Is there a way to accomplish this with your formula?

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

I think the problem is "*". It become the concatenated lookup value and an asterisk.

I typed it all in and when i check it wil F9 it gives the answer I expect, but when I hit return it gives me a #value error. Do you think it is because I am referencing another worksheet for the if condition?

Hello I need Help, its working if the reference data is in the same sheet. I have 60000+ values columns. So i was using it in the different sheet then your formula is not working.

Below is the formula i was using. My 'Seller' Name is in F2 and total reference data is in Sheet 9

=IFERROR(INDEX(Sheet9!B:B, SMALL(IF(F$2=Sheet9!A:A, ROW(Sheet9!B:B)-2,""), ROW()-2)),"")

Does your reference data in sheet 9 starts row 1?

This is exactly what I need, but I can't get it to work

In one tab I have Recipes

i.e Cottage Pie Mince Meat

Veg

Potatoes

In the other tab I have what to order

I want it to look up the recipe title and pull through the full list of ingredient but it is completely rejecting the formula

Can you help please?

This is as far as I got

iferror(index('WHAT TO ORDER'!A:A,small(if(1(--(RECIEPES!D:D))*(--('WHAT TO ORDER'!A:A)),row(RECIEPES!H:H)-2,""),row()-2,"")))

Specify your Column. Ex. A1:A1000.

You can use offset instead of index. Specially when working with large sets of data.

Could you please suggest a formula for Multiple values in on column?

The formula works for me but after few columns it stops bringing more values. I have 22 values in my table but it only pulls up to 12 values.

Hi, Could you please help me

In column A i have list of entities like Row1)coco-cola, Row2)AT&T, Row3) CSC and so on..... and in column B i have list of Index names like CDX-EM-27v1, CDX-EM-Diversified, etc. one entity could be a part of more than 2 indexes. is there any way i can get the names of the indexes against each entity in column C

Inside the IF, why do you need ROW($B$3:$B$13)-2 ? Why not just write 1?

Hi Manrique,

Because we are dealing with an array formula, this expression returns an array like {1;2;3;4;5;6;7;8;9;10;11} where each number is a relative position of a row in the specified range. We subtract 2 because our first cell is in row 3, but it's relative position in the range is 1. The above array goes to the value_if_true argument of the IF function. 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.

I have been working on a formula for a couple of months now, and even after asking several people, no one knows the answer. I'm thinking I may need to use INDEX, but if that is the case I can't get it to work. I have a spreadsheet with items that have their names listed 3 times in column A. In Column B I have 3 different sizes for each name. For example, A2 says Apple, B2 says 16, A3 says pear, B3 says 32, etc. I need the formula to calculate the quantity in column C for each item name and size respectively. I have managed to get it to calculate the quantity for the size 16 (in oz.) for the apple, for instance, but it adds that quantity for every single 16 on the spreadsheet, and I need the to add separately. I think it may be because I have each item name listed multiple times with the same size, but I would think there would be a way around this. Can anyone help if that's not to confusing? Thanks!

I'm sorry, it should say A3 says Apple in my example instead of pear.

Hi,

I tried to follow along but it is not working for my spreadsheet. I am currently using the below formula in column AT but it is not working:

=INDEX($BA$2:$BA$10000,(IF(1=((--($AI2=$AX$2:$AX$10000))*(--($AP2=$AY$2:$AY$10000))*(--($AQ2=$AZ$2:$AZ$10000))),ROW($BA$2:$BA$10000)-1,"")))

I have data in columns AX, AY, AZ, and BA. This data goes from row 2-row 100 (so AX2-AX100 has info, etc). I also have data in columns AI, AP, and AQ. If the data in AI, AP, and AQ matches AX, AY, and AZ, then I want AT to generate the number that is in BA.

Example of what my excel looks like:

I need a formula in AT:

Row 1: AI AP AQ AT AX AY AZ BA

Row 2: 123 BBB SS 124 CCC TT 1001

Row 3: 124 CCC TT 125 DDD UU 1002

Row 4: 125 DDD UU 126 FFF WW 1003

Row 5: 0 EEE VV 123 BBB SS 1004

Row 6: 126 FFF WW 0 EEE VV 1005

I am hoping the formula in AT2 would make 1004.

Thank you!