# 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 1calleThe formula is a bit more compact, but the 2North Dakotaone 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.

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 3third, 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 1callethe 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.

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.

## FAQs

### How do you find multiple match results in Excel? ›

Excel allows us to return multiple match result of a lookup using the TEXTJOIN and IF functions.
...
To apply the formula, we need to follow these steps:
1. Select cell E3 and click on it.
2. Insert the formula: =TEXTJOIN(",", TRUE, IF(B3:B13=F3, C3:C13, ""))
3. Press Ctrl+Shift+Enter to enter the array function.

How to VLOOKUP multiple values in Excel with one or more criteria? ›

Follow these steps to use VLOOKUP with multiple values:
1. Create a specific helper column on the table's left. ...
2. Type your starting formula in the specific cell. ...
3. Add the multiple search values. ...
4. Input the table array. ...
5. Pick a range lookup option.
Sep 28, 2022

Is there a VLOOKUP for multiple criteria? ›

The VLOOKUP function with multiple criteria is used to search for value in a column and return the value from a corresponding column. The VLOOKUP function with multiple criteria searches for the lookup_value in the first column of the given array/table.

What happens when VLOOKUP finds multiple matches? ›

In Excel, VLOOKUP cannot natively return multiple values from multiple matches. Use FILTER to look up all the matches and return the corresponding values. The value that is returned from the formula.

Can Xlookup return multiple matches? ›

One more amazing feature of XLOOKUP is its ability to return more than one value relating to the same match.

How do you index match multiple matches? ›

How to Use INDEX MATCH With Multiple Criteria in Excel
1. INDEX MATCH multiple criteria example.
2. Step 1: Insert a normal INDEX MATCH formula.
3. Step 2: Change the MATCH lookup value to 1.
4. Step 3: Write the criteria.
Aug 4, 2022

How do I do a VLOOKUP with 3 conditions? ›

VLOOKUP with Multiple Criteria – Using a Helper Column
1. Insert a Helper Column between column B and C.
2. Use the following formula in the helper column:=A2&”|”&B2. ...
3. Use the following formula in G3 =VLOOKUP(\$F3&”|”&G\$2,\$C\$2:\$D\$19,2,0)
4. Copy for all the cells.

Why use index match instead of VLOOKUP? ›

While VLookup is great for searching for fields where you know the row, Index Match is much more flexible as you can search by row, or by column, or by both. In these situations its best to use Index Match instead of VLookup.

How do I use multiple Xlookup matches? ›

How do I get XLOOKUP to return all matches?
1. Enter the FILTER function.
2. Define the first argument, return_array.
3. Use an expression as the second argument: lookup_array = lookup_value.
4. The formula returns all matching records.
Sep 23, 2022

Can Xlookup be used instead of VLOOKUP? ›

XLOOKUP was released by Microsoft in 2019 and is meant as the replacement for VLOOKUP, HLOOKUP, INDEX/MATCH functions.

### Can you do a partial match VLOOKUP? ›

The VLOOKUP function supports wildcards, which makes it possible to perform a partial match on a lookup value. For instance, you can use VLOOKUP to retrieve values from a table based on typing in only part of a lookup value.

How can you handle multiple matches in lookup transformation? ›

If your lookup condition is not based on a key, or your lookup source is denormalized, the Server might find multiple matches.
...
The Lookup transformation offers three choices:
1. Use the value in the first matching record in the lookup cache.
2. Use the value in the last matching record in the lookup cache.
3. Return an error.
Oct 16, 2020

What are the limitations of Xlookup? ›

Does Xlookup have a limit? The Xlookup function doesn't have a limit. This means you can use all 1,048,576 rows and 16,384 columns of a workbook.

Does VLOOKUP retrieve all matching values? ›

Well, VLOOKUP is designed to return a single value, not multiple values. That is, VLOOKUP scans down the lookup range and stops at the first matching row … ignoring any additional matching rows. Once VLOOKUP finds a matching row, it shoots to the right to retrieve the related value from a single column.

Can I use index match to return multiple values? ›

This particular formula returns all of the values in the range B2:B11 where the corresponding value in the range A2:A11 is equal to the value in cell D2. The following example shows how to use this formula in practice.

Is index match faster than Xlookup? ›

In our table, the VLOOKUP formula calculated in 2.3 seconds, INDEX MATCH in 2.6 seconds, OFFSET and MATCH in 2.7 seconds, and XLOOKUP in 3.3 seconds. As you see, the calculation speed increases significantly compared to ranges. Interestingly enough, Excel tables are very fast even with regular references.

When should you not use VLOOKUP? ›

If you have a table where you want to return an entry based on a value that is not in the leftmost column, you cannot use Vlookup. Alternatively, you can reorganize your table and change the column order to have the column that you need as the leftmost column.

Is Xlookup lighter than INDEX match? ›

Summary of the performance of XLOOKUP

XLOOKUP is much more powerful than VLOOKUP or INDEX/MATCH. It offers a great range of options right built-in.

When should you not use an INDEX? ›

When Should Indexes Be Avoided?
1. Indexes should not be used on small tables.
2. Indexes should not be used on columns that return a high percentage of data rows when used as a filter condition in a query's WHERE clause. ...
3. Tables that have frequent, large batch update jobs run can be indexed.

What is the difference between VLOOKUP and Xlookup in Excel? ›

XLOOKUP vs VLOOKUP – Key Differences

XLOOKUP searches for data both horizontally and vertically. VLOOKUP searches only vertically. XLOOKUP can refer to the left of the lookup_value. It always looks up the values towards the right of the lookup_value.

### What is better than Xlookup? ›

Vlookup is easier to grasp and often all you really need. Index/Match can search right-to-left or left-to-right and doesn't require you select as large an array in most cases. No matter what side of the fence you're on with that debate, XLOOKUP seems to have outdone them BOTH.

What is substitute for Xlookup? ›

Well, the most common use of XLOOKUP is to look up a value in a table. With that in mind, there are four different ways you can produce a similar result: INDEX/MATCH, VLOOKUP, FILTER, and QUERY.

What is the new alternative to VLOOKUP? ›

1. XLOOKUP Function. If you have a new version of Excel, then the XLOOKUP Function is probably the best alternative to the VLOOKUP Function.

Can you do a fuzzy match in Excel? ›

The Fuzzy Lookup Add-In for Excel was developed by Microsoft Research and performs fuzzy matching of textual data in Microsoft Excel. It can be used to identify fuzzy duplicate rows within a single table or to fuzzy join similar rows between two different tables.

Can you do a partial match in Excel? ›

Another way to force Excel IF to work for partial text match is to include either the FIND or SEARCH function in the logical test. The difference is that FIND is case-sensitive while SEARCH is not. As both functions are designed to perform a "cell contains" type of match, wildcards aren't really needed in this case.

How do you list all matched instances in Excel? ›

1. Select a blank cell to output the first matched instance, enter the below formula into it, and then press the Ctrl + Shift + Enter keys simultaneously. Note: In the formula, B2:B11 is the range which the matched instances locate in. A2:A11 is the range contains the certain value you will list all instances based on.

How do I extract multiple matches from different columns? ›

This is an array formula and must be entered with Control + Shift + Enter. After you enter the formula in the first cell, drag it down and across to fill in the other cells.

What is the difference between VLOOKUP and index match in Excel? ›

The main difference between VLOOKUP and INDEX MATCH is in column reference. VLOOKUP requires a static column reference whereas INDEX MATCH requires a dynamic column reference. With VLOOKUP you need to manually enter a number referencing the column you want to return the value from.

How do I use Xlookup to find matches? ›

XLOOKUP Function Syntax

[match_mode] – Here you can specify the type of match you want: 0 – Exact match, where the lookup_value should exactly match the value in the lookup_array. This is the default option. -1 – Looks for the exact match, but if it's found, returns the next smaller item/value.

How do I find different occurrences in Excel? ›

You can use the combination of the SUM and COUNTIF functions to count unique values in Excel. The syntax for this combined formula is = SUM(IF(1/COUNTIF(data, data)=1,1,0)). Here the COUNTIF formula counts the number of times each value in the range appears.

### What is the formula for multiple criteria? ›

Another way to get an Excel IF to test multiple conditions is by using an array formula. To complete an array formula correctly, press the Ctrl + Shift + Enter keys together. In Excel 365 and Excel 2021, this also works as a regular formula due to support for dynamic arrays.

Can you do Xlookup for multiple columns? ›

One advantage of the XLOOKUP Function is that it can return multiple consecutive columns at once. To do this, input the range of consecutive columns in the return array, and it will return all the columns.

How do you use the filter function with multiple criteria? ›

To filter and extract data based on multiple complex criteria, you can use the FILTER function with a chain of expressions that use boolean logic. For example, the generic formula below filters based on three separate conditions: account begins with "x" AND region is "east", and month is NOT April.

Top Articles
Latest Posts
Article information

Author: Reed Wilderman

Last Updated: 09/12/2023

Views: 6065

Rating: 4.1 / 5 (72 voted)

Author information

Name: Reed Wilderman

Birthday: 1992-06-14

Address: 998 Estell Village, Lake Oscarberg, SD 48713-6877

Phone: +21813267449721

Job: Technology Engineer

Hobby: Swimming, Do it yourself, Beekeeping, Lapidary, Cosplaying, Hiking, Graffiti

Introduction: My name is Reed Wilderman, I am a faithful, bright, lucky, adventurous, lively, rich, vast person who loves writing and wants to share my knowledge and understanding with you.