In Excel, find multiple matches of one or more criteria (2023)

The tutorial shows some ways to find multiple matches in Excel based on one or more conditions and return multiple results in a single column, row, or cell.

When using Microsoft Excel for data analysis, you can often find yourself in situations where you need all the corresponding values ​​for an ID, name, email address, or other unique identifier. An immediate solution that comes to mind is to use Excel's VLOOKUP function, but the problem is that it can only return a single match.

La búsqueda de múltiples valores se puede realizar mediante el uso combinado de múltiples funciones. Si está lejos de ser un experto en Excel, no se apresure a abandonar esta página. Haré todo lo posible para explicar la lógica subyacente para que incluso un principiante pueda entender las fórmulas y adaptarlas para resolver problemas similares. Aún mejor, le mostraré otra posible solución que solo requiere unos pocos clics del mouse y no requiere ningún conocimiento de fórmulas de Excel.

• Find multiple values ​​using a formula
  • Vlookup to return multiple values ​​in columns
  • Vlookup to return multiple matches in rows
  • Find multiple matches based on multiple criteria
• Vlookup to return multiple results in one cell (comma or separated)

How to do multiple virtual lookups in Excel using one formula

As mentioned at the beginning of this tutorial, there is no way to do this.Excel SVERWEISreturn multiple values. The task can be performed with the following functions in oneArray-Formel:

• SE- evaluates the condition and returns a value if the condition is met and another value if the condition is not met.
• KLEIN- 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.
• SEERRO- trapping errors.

Below are some examples of such formulas.

Formula 1. Find multiple matches and return results in one column

Assuming you have the names of the vendors in column A and the products they sold in column B, column A contains a few occurrences of each vendor. Your goal is to get a list of all products sold by a specific person. To do this, follow these steps:

1. Enter a list of unique names in a blank row on the same or a different worksheet. In this example, the names are entered in cells D2:G2:
   

   Principal.To quickly get all the different names in a list, you can use a formulaextract different values ​​in excel.

2. Below the first name, select a number of blank cells equal to or greater than the maximum number of possible matches, enter one of the following array formulas in the formula bar, and pressCtrl + Shift + Enterto complete it (in this case the formula can only be edited in the entire area where it was entered). Or you can enter the formula in the first cell, pressCtrl + Shift + Enterand drag the formula to a few more cells (in this case, you can edit the formula in each cell individually).

   =IF.ERROR(INDEX($B$3:$B$13, SMALL(IF(D$2=$A$3:$A$13, LINE($B$3:$B$13)-2,""), LINE() -2 )), "")

   o

   =IF.ERROR(INDEX($B$3:$B$13,SMALL(IF(D$2=$A$3:$A$13,LINE($A$3:$A$13)-MIN(LINE($A$3:$ A$13) ) ))+1,""), REIHE()-2))),"")

   As you can see, the 1^calleThe formula is a bit more compact, but the 2^{North Dakota}one is more universal and requires less modification (let's dig a little deeper into the syntax and logic).

3. Copy the formula to other columns. To do this, select the range of cells where you just entered the formula and drag the fill handle (a small square in the lower right corner of the selected range) to the right.

The result looks like this:

How does this formula work?

This is an example of using intermediate to advanced Excel assuming basic knowledgearray formulasand Excel functions. Work from the inside out, and this is what you do:

1. IF function

   At the heart of the formula, it uses the IF function to get the positions of all occurrences of the lookup value in the lookup scope: IF(D$2=$A$3:$A$13, ROW($B$3:$B $13) ) -two,"" )

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

   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 the return range starts at row 2, 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 requires no modification, regardless of the position of the return column. In this example, it would be LINE($A$3:$A$13)- MIN(LINE($A$3:$A$13))+1.

   See Also

   VLOOKUP with multiple criteria

   So at this point you have an array made up of numbers (match locations) and empty strings (no matches). For cell D3 in this example we have the following matrix:
   

   If you look at the source data, you'll see that "Adam" (lookup value in D2) appears in all 3^third, 8^ºmi 10^ºPositions in the search area (A3:A13).

2. SMALL function
   Then the SMALL(array, k) function comes into play to determine which of the matches in a given cell should be returned.

   ComLineAlready established, let's figure it outkArgument, that is, the kth smallest value to return. To do this, you do a sort of "counter increment" ROW()-n, where "n" is the row number of the first cell in the formula minus 1. In this example, we put 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), and so on.

   As a result, the SMALL function extracts the 1^callethe smallest array element in cell D3, the second smallest element in cell D4, and so on. And this turns the initially long and complex formula into a very simple one, like this:
   

   Principal.To display the calculated value behind a specific part of the formula, select that part in the formula bar and pressF9.

3. INDEX function

   This part is easy. It uses the INDEX function to return the value of an array element based on its row number.

4. SEERRO function

   And finally, wrap the formula in theIFERROR functionto deal with possible errors that are unavoidable because you cannot know how many matches will be returned for this or that lookup value and therefore copy the formula into a number of cells equal to or greater than the number of possible matches. In order not to scare your users with a bunch of errors, just replace them with an empty string (blank cell).

Observation.Consider the intended use ofabsolutemirelativeCell references in the formula. All references are fixed except the relative column reference in the lookup value (D$2), which must change based on the relative position of one or more columns that the formula is copied to to return matches for other lookup values.

Putting it all together, we get the following generic formulas for getting multiple values ​​in Excel:

Formula 1:

SEERRO(INDEX(return zone, SMALL(IF(fetch-wert=search area, LINE(return zone)-METRO,""), LINE() -norte)), "")

Formula 2:

SEERRO(INDEX(return zone, SMALL(IF(fetch-wert=search area, LINE(search area) - MIN(LINE(search area))+1,""), LINE() -norte)), "")

Wo:

• METROis the row number of the first cell in the return range minus 1.
• norteis the row number of the first cell of the formula minus 1.

Observation.Both in the previous examplenortemiMETROthey are equal to "2" because our return range and our formula range start at row 3. In your spreadsheets, these can be different numbers.

Formula 2. Find multiple matches and return results one by one

If you want to return multiple values ​​in rows instead of columns, change the above formulas like this:

=IF.ERROR(INDEX($B$3:$B$13, SMALL(IF($D3=$A$3:$A$13, LINE($B$3:$B$13)-2,""), COLUMN() -4 )), "")

O

=IF.ERROR(INDEX($B$3:$B$13,SMALL(IF($D3=$A$3:$A$13,LINE($A$3:$A$13))-MIN(LINE($A$3: $A) $13 ))+1,""),SPALTE()-4)), "")

As in the example above, both are array formulas, so remember to press the keyCtrl + Shift + Entershortcut to complete them correctly.

The formulas work with the same logic as the previous example, except that it uses the COLUMN function instead of the ROW function to determine which corresponding value to return 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 entered in cells E2:H2. where e is 5^ºTo share,norteis equal to "4" (5-1=4).

Observation.For the formula to copy correctly to other rows, be aware of the lookup value, absolute column, and relative row references, for example B.$D3.

In short, here are generic formulas for Vlookup with multiple results returned in rows:

Formula 1:

SEERRO(INDEX(return zone, SMALL(IF(fetch-wert=search area, LINE(return zone) -METRO, ""), TO SHARE() -norte)), "")

Formula 2:

SEERRO(INDEX(return zone, SMALL(IF(fetch-wert=search area, LINE(search area) - MIN(LINE(search area))+1,""), COLUMN() -norte)), "")

Wo:

• METROis the row number of the first cell in the return range minus 1.
• norteis the column number of the first cell of the formula minus 1.

Formula 3. Find multiple matches based on multiple conditions

You now know how to use a condition to search for multiple values ​​in Excel. But what if you want to get multiple matches based on two or more criteria? Continuing with the examples above, what if you have an extraMesColumn and want to get a list of all products sold by a specific seller in a given month?

If you are familiar with array formulas, remember that they allow the use of asterisks (*) as charactersAND operator. So you can simply take the formulas discussed in the previous two examples and have them check for various conditions as shown below.

Return multiple matches in a column

SEERRO(INDEX(return zone, SMALL(IF(1=((--(lookup_value1=lookup_range1)) * ( --(lookup value2=research area2))), LINE(return zone)-METRO,""), LINE()-norte)), "")

Wo:

• METROis the row number of the first cell in the return range minus 1.
• norteis the row number of the first cell of the formula minus 1.

Suppose the provider list (search area1) is in A3:A30, the list of months (research area2) is at 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 form:

=SEERRO(INDEX($C$3:$C$30, SMALL(SE(1=((-($E$3=$A$3:$A$30))) * (--($F$3=$B$3) : $B$30))), ARRAY($C$3:$C$30)-2,""), ARRAY()-2)),"")

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

Return multiple results in a row

If you want to retrieve multiple values ​​based on multiple sets of criteria, you might prefer the horizontal layout, which returns results in rows. In that case, use the following general formula:

SEERRO(INDEX(return zone, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup value2=research area2))), LINE(return zone) -METRO, ""), TO SHARE() -norte)), "")

Wo:

• METROis the row number of the first cell in the return range minus 1.
• norteis the row number of the first cell of the formula minus 1.

For our sample data set, the formula is as follows:

=IF.ERROR(INDEX($C$3:$C$30, SMALL(IF(1=((-($E3=$A$3:$A$30))) * (--($F3=$B$3: $) B $30))), ZEILE($C$3:$C$30)-2,""), BACK()-6))),"")

And the result could look like this:

Also, you can perform multiple Vlookups with three, four, or more conditions.

How do these formulas work?

Basically, the formulas for vlookup multiple values ​​with multiple conditions work with the already known logic, which is basically explainedfirst example. The only difference is that the IF function now tests for multiple conditions:

1=((--(lookup value1=search area1))*(--(lookup value2=research area2))*…)

The result of each lookup_value=lookup_range comparison is an array of TRUE (condition met) and FALSE (condition not met) logical values. The double unary operator (--) forces the logical values ​​to be 1 and 0. And since multiplying by zero always yields zero, in the resulting array you only have 1 for elements that meet all of the specified conditions. Now just compare the last array to number 1, so the ROW function will return the number of rows that meet all conditions, otherwise an empty string.

a word of caution. All the Vlookup formulas discussed in this tutorial arearray formulas. Therefore, each formula iterates through each element of the arrays each time the source data is changed or the spreadsheet is recalculated. For large spreadsheets with hundreds or thousands of rows, this can slow down your Excel significantly.

How to find multiple matches in Excel using formulas. If you want to take a look at the examples and possibly reverse engineer the formulas for better understanding, you can download them.Excel Vlookup Multiple Valuessample worksheet.

How to return multiple values ​​in one cell with Vlookup

I'll be honest: I don't know of any formula for vlookup duplicates producing multiple matches in a single sale. However, I know of a formula-free (read "no-fuss") way to do this using two supplements included in ourUltimate Suite for Excel. If you don't already have it in your Excel, you can download a free 14-day trial.Hereand then follow the steps below.

Source data and expected result

As shown in the screenshot, we continue to work with the dataset we used in the previous example. But this time we want to achieve something different: Instead of putting multiple matches in separate cells, we want them to appear in a single sale, separated by a comma, space, or other separator of your choice.

Drag rows with multiple matches to the main table

In your main table, enter a list of unique names in the first column and months in the second column and arrange them as per the screenshot below. After that, follow these steps:

1. Select your main table or click any cell in it, then click the buttonmerge two tablestape button:
   
2. The plugin is smart enough to identify and select the entire table, so all you have to do is clickNext:
   

   Principal.When using the tool for the first time, it makes sense to choose theMake a backup of the worksheetbox in case something goes wrong.

3. Select the lookup table and clickNext.
   
4. Select one or more pairs of matching columns to compare in the main table and in the lookup table (in this example, it is thesellermiMesColumns) and then clickNext.
   
5. Select the column(s) for which you want to retrieve the corresponding values ​​(productsin this example) and clickNext.
   
6. Tell the plugin exactly how to order multiple matches in the main table. For this example, we need the following option:Insert rows with duplicate values ​​after row with the same value. Make sure no other options are selected and clickmovie.
   

At this point, you have the following output: All matching rows are placed in the main table and grouped by the values ​​in the lookup columns, first by salesperson, then by month:

The resulting table looks good, but it's not quite what we wanted, is it? As you remember, we searched for multiple matches and returned them in a single sale, comma or otherwise separated.

Merge duplicate lines into a single line

To merge "duplicate lines" into a single line, we use another tool:Merge Row Wizard.

1. Select the table created by the Merge Tables tool (see screenshot above) or any table cell and clickcombine linestape button:
   
2. Verify that the plugin has the correct table and clickNext:
   
3. Select the key column or columns (sellermiMesin this example) and clickNext:
   
4. Select columns containing multiple matches (productsIn this example, select the desired delimiter (semicolon, comma, space, or newline) and clickmovie.

   Optionally, you can enable one or both of the additional features:

   • eliminate duplicate values- If the column to be merged contains some identical values, the first occurrence is kept and duplicate matches are removed.

     In this example, we do not enable this option and the plugin returns all matches found. For example, in cell C2 we have this string:Lemons, bananas, apples, lemons, bananas(See the result in step 5 below.) If you want to exclude duplicates, the result would be:lemons, bananas, apples.

   • Ignore empty cells- self explanatory :)
     
5. Wait a few seconds for the plugin to process and voila!
   

See how you can use our tools to find and return multiple values ​​in Excel. If you're curious to try them out, a fully functional Ultimate Suite trial is available for download below.

downloads available

Vlookup Multiple Values ​​- Examples(.xlsx file)
Ultimate Suite - trial version(.exe file)

Other ways to search for V in Excel

• Merge Table Wizard- Quick and easy way to do vlookup in excel.
• Search in Excel: Examples of Functions and Formulas- a handful of formulas for different types of searches
• INDEX MATCH as a better alternative to Vlookup- a powerful and versatile formula for searching in Excel.
• Examples of VLOOKUP formulas- Bidirectional search, nested multicriteria search and more.
• Vlookup with Sum and Sumif- Find and add the corresponding values.