## How to list all matched instances of a value in Excel?

As the left screenshot shown, you need to find and list all match instances of value “Linda” in the table. How to achieve it? Please try the methods in this article.

**List all matched instances of a value with array formula Easily list only the first matched instance of a value with Kutools for Excel**

More tutorials for VLOOKUP...

#### **List all matched instances of a value with array formula**

With the following array formula, you can easily list all match instances of a value in a certain table in Excel. Please do as follows.

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.

**=INDEX($B$2:$B$11, SMALL(IF($D$2=$A$2:$A$11, ROW($A$2:$A$11)-ROW($A$2)+1), ROW(1:1)))**

**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. And D2 contains the certain value.

2. Keep selecting the result cell, then drag the Fill Handle down to get the other matched instances.

#### **Easily list only the first matched instance of a value with Kutools for Excel**

You can easily find and list the first matched instance of a value with the **Look for a value in list** function of **Kutools for Excel** without remembering formulas. Please do as follows.

Before applying** Kutools for Excel**, please **download and install it firstly**.

1. Select a blank cell you will place the first matched instance, then click **Kutools** > **Formula Helper** > **Formula Helper**.

2. In the **Formulas Helper** dialog box, you need to:

**Look for a value in list**option in the

**Choose a formula**box;

**Tips**: You can check the

**Filter**box, enter the keyword into the textbox to quickly filter the formula you need.

**Table_array**box, select the table range which contains both the specific value column and matched instances column;

**Look_value**box, select the cell with the specific value you will list the first instance based on;

**Column**box, select the column contains the matched instance. Or just enter the column number into it;

**Tips**: The column number is based on the selected number of columns, if you select four columns, and this is the 3rd column, you need to enter number 3 into the

**Column**box.

**OK**button.

Then the first matched instance of the given value is listed as below screenshot shown.

If you want to have a free trial (30-day) of this utility, **please click to download it**, and then go to apply the operation according above steps.

#### related articles

**Vlookup values across multiple worksheets**

You can apply the vlookup function to return the matching values in a table of a worksheet. However, if you need to vlookup value across multiple worksheets, how can you do? This article provides detailed steps to help you easily solve the problem.

**Vlookup and return matched values in multiple columns **

Normally, applying the Vlookup function can only return the matched value from one column. Sometimes, you may need to extract matched values from multiple columns based on the criteria. Here is the solution for you.

**Vlookup to return multiple values in one cell **

Normally, when applying the VLOOKUP function, if there are multiple values that match the criteria, you can only get the result of the first one. If you want to return all matched results and display them all in a single cell, how can you achieve?

**Vlookup and return entire row of a matched value **

Normally, using the vlookup function can only return a result from a certain column in the same row. This article is going to show you how to return the whole row of data based on specific criteria.

**Backwards Vlookup or in reverse order**

In general, the VLOOKUP function searches values from left to right in the array table, and it requires the lookup value must stay in the left side of target value. But, sometimes you may know the target value and want to find out the lookup value in reverse. Therefore, you need to vlookup backwards in Excel. There are several ways in this article to deal with this problem easily!

### Best Office Productivity Tools

Supports Office/Excel 2007-2021 and 365 | Available in 44 Languages | Easy to Uninstall Completely

**Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need Is Just A Click Away...**

**Supercharge Your Excel Skills: Experience Efficiency Like Never Before with Kutools for Excel** (Full-Featured 30-Day Free Trial)

#### Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day! (Full-Featured 30-Day Free Trial)