How to Vlookup multiple values in Excel with criteria

Formula 1. Vlookup multiple matches 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:

1. 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 the UNIQUE function in Excel 365 or a more complex formula to extract distinct values in older versions.

2. 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 drag the formula down to a few more cells (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).

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

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

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

Formula 1:

Formula 2:

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.

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

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:

1. Select your main table or click any cell within it, and then click the Merge Two Tables button on the ribbon:
   
2. 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.

3. Select the lookup table, and click Next.
   
4. 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.
   
5. Select the column(s) from which you want to pull matching values (Product in this example), and click Next.
   
6. 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.

Combine duplicates rows into one row

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

1. 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:
   
2. Check if the add-in's got the table right, and click Next:
   
3. Select the key column or columns (Seller and Month in this example), and click Next:
   
4. 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 :)
     
5. Allow the add-in a few seconds for processing, and you are all set!