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.
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:
Tips: You can check the Filter box, enter the keyword into the textbox to quickly filter the formula you need.
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.
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
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
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!