In this article, I will discuss how to find multiple values in Excel. Often, while working with spreadsheets, finding multiple values at once can be a great help. For instance, we have a dataset containing the hobbies of several people. However, in this dataset, one person (**Emily**) has more than one hobby. So, now we will use several excel tools and functions to get the multiple hobbies of Emily at once. Besides that, I will show how to join multiple values in a single cell.

**Table of Contents**hide

**Download Practice Workbook**

You can download the practice workbook that we have used to prepare this article.

**8 Methods to Find Multiple Values in Excel**

**1. Use Find and Replace Tool to Get Multiple Values in Excel**

You can get multiple values very easily by using the** Find** feature of the **Find and Replace **tool of **MS Excel**. In our dataset, the name Emily is mentioned **3** times. So, follow the below steps to find these **3 **values at once.

**Steps:**

- First, select the dataset (
**B4:C11**).

- Next, press
**Ctrl**+**F**to bring up the**Find and Replace**window or go to**Home**>**Editing**group >**Find & Select**>**Find**. - Then, type ‘
**Emily**’ in the**Find what**field and click on the**Find All**.

- As a result, we have found
**3**names (**Emily**) listed in the below window.

**Read more:** **How to Find Value In Range in Excel (3 Methods)**

**2. Excel Filter Option to Find Multiple Values**

Another easy and quick option to get multiple values in excel is to use the **Autofilter**. Let’s have a look at the steps involved in this method.

**Steps:**

- First, right-click on the cell to which you want to apply the filter. I have selected
**Cell B5**, as I need to filter all the names,**Emily**. - Then go to
**Filter**>**Filter by Selected Cell’s Value**.

- Consequently, all the cells containing the name
**Emily**are filtered as below.

- Now, if you want to undo the filtering, just click on the
**Autofilter**icon of the dataset header, select**Clear Filter From “Name”**and click**OK**.

**Read more:** **How to Find a Character in String in Excel**

**3. Apply Advanced Filter Option to Return Multiple Values**

Excel has a filtering option named **Advanced Filter**. This option is very useful while finding multiple values. You have to set a criteria range to apply the **Advanced Filter **option. Let’s go through the steps involved in this method.

**Steps:**

- First, set the criteria range (
**B13:C14**).

- Next, go to
**Data**>**Sort & Filter**>**Advanced**.

- As a consequence, the
**Advanced Filte**r window will show up. Now, set the**List range**(**Dataset range**) and**Criteria range**and click**OK**.

- Finally, here we got all of Emily’s hobbies at once.

⏩ **Note**

Remember, the **Header **of the main dataset and the **Criteria range** have to be similar, otherwise, the **Advanced Filter** option will not work.

**4. Return Multiple Values by Using Excel Defined Table**

We can create **Excel Defined Tables** and thus apply filtering to get multiple values. This is a very convenient and easy way to find multiple values.

**Steps:**

- First, click on any of the cells of the dataset (
**B4:C11**).

- Next, press
**Ctrl**+**t**from the keyboard. As a consequence, the**Create Table**window will show up. Check the table range and click**OK**.

- As a result, we have the below table created from our dataset.

- Now, click on the down arrow icon next to the header of the table. Then, check the name
**Emily**and click**OK**

- Eventually, here is our expected filtered result.

**Similar Readings:**

**How to Find Text in Cell in Excel****Excel Search for Text in Range (11 Quick Methods)****How to Find If Cell Contains Specific Text in Excel****Find Character in String Excel (8 Easy Ways)**

**5. Insert FILTER Function to Find Multiple Values**

This time we will use** the FILTER function **to return the multiple values in excel.

**Steps:**

- First, type the below formula in
**Cell C14**.

`=FILTER(C5:C11,B5:B11=B14)`

- Next, hit
**Enter**. - Consequently, all the hobbies of Emily are returned at once.

⏩**Note**

➤ The** FILTER** function is only available to **Excel 365 **subscribers.

**6. Search Multiple Values with INDEX Function in Excel**

You can find multiple values using **the INDEX function** along with some other excel functions. This formula to get multiple values is complex. The formula is entered as an array. Anyways, I will explain the formula below. Before that, let’s go through the steps of this method.

**Steps:**

- Initially, type the following formula in
**Cell C14**.

`=INDEX($B$5:$C$11,SMALL(IF($B$5:$B$11=$B$14,ROW($B$5:$B$11)),ROW(1:1))-4,2)`

** **

- As a result, we received the below result.

- Next, drag down the
**Fill Handle**(**+**) sign to get the other values.

- As a consequence, here is the list of Emily’s hobbies we got.

**🔎 How does the Formula Work?**

**IF($B$5:$B$11=$B$14,ROW($B$5:$B$11))**

Here,** the IF function** returns a row number if a cell range **B5:B11** is equal to** B14**, otherwise it returns** FALSE**.

**SMALL(IF($B$5:$B$11=$B$14,ROW($B$5:$B$11)),ROW(1:1))**

Now, this part of the formula uses **the SMALL function** which returns the **nth **smallest value. This formula will return the numbers: **5**,**8**,**11**.

**INDEX($B$5:$C$11,SMALL(IF($B$5:$B$11=$B$14,ROW($B$5:$B$11)),ROW(1:1))-4,2)**

Now comes the final part of the formula. We know, the **INDEX **function returns the value at a given position. Another thing is, the **INDEX** function considers the first row of our table as row 1. As my table dataset starts in row** 5**, I have subtracted **4** from the **ROW** value to get the correct row from the dataset. So, for the array** B5:C11**, row numbers **5**,**8**,**11**, and column no **2**, the **INDEX** function will provide our desired result

**📌 Hide the Errors Generated by Above Formula**

There is a problem with the above-mentioned **INDEX** formula. When you drag down the **Fill Handle** (**+**) sign, the formula returns an error (**#NUM!**) after a certain value. So, to fix the above formula we will use the **IF** and** ISERROR** functions.

**Steps:**

- First, type the below formula in
**Cell C14**.

`=IF(ISERROR(INDEX($B$5:$C$11,SMALL(IF($B$5:$B$11=$B$14,ROW($B$5:$B$11)),ROW(1:1))-4,2)),"",INDEX($B$5:$C$11,SMALL(IF($B$5:$B$11=$B$14,ROW($B$5:$B$11)),ROW(1:1))-4,2))`

** **

- As a result, we will get the result free of any errors.

Here, the** ISERROR **function checks whether a value is an error, and returns TRUE or FALSE. The above formula wrapped with** IF **and** ISERROR **functions check whether the result of the array is an error or not and thus returns blank (“”) if the result is an error, otherwise it returns the corresponding value.

**7. User Defined Function to Find Multiple Values in Excel (VBA)**

In this method, we will discuss how to use the** User Defined Function **to get multiple values in excel. Here, we will use the** User Defined Function**: **vbaVlookup**.

**Steps:**

- Firstly, go to the active worksheet.
- Secondly, go to
**Developer**>**Visual Basic**.

- Then the
**Visual Basic**window will show up. Go to the**VBA Project**corner (Upper left corner of the window). - Thirdly, right-click on the Project name and go
**Insert**>**Module**.

- As a result, you will get the
**Module**. Write the below code on the**Module**.

```
'Name User Defined Function and arguments
Function vbaVlookup(lookup_value As Range, tbl As Range, col_index_num As Integer, Optional layout As String = "v")
'Declare variables and data types
Dim r As Single, Lrow, Lcol As Single, temp() As Variant
'Redimension array variable temp
ReDim temp(0)
'Iterate through cells in cell range
For r = 1 To tbl.Rows.Count
'Check if lookup_value is equal to cell value
If lookup_value = tbl.Cells(r, 1) Then
'Save cell value to array variable temp
temp(UBound(temp)) = tbl.Cells(r, col_index_num)
'Add another container to array variable temp
ReDim Preserve temp(UBound(temp) + 1)
End If
Next r
'Check if variable layout equals h
If layout = "h" Then
'Save the number of columns the user has entered this User Defined Function in.
Lcol = Range(Application.Caller.Address).Columns.Count
'Iterate through each container in array variable temp that won't be populated
For r = UBound(temp) To Lcol
'Save a blank to array container
temp(UBound(temp)) = ""
'Increase the size of array variable temp with 1
ReDim Preserve temp(UBound(temp) + 1)
Next r
'Decrease the size of array variable temp with 1
ReDim Preserve temp(UBound(temp) - 1)
'Return values to worksheet
vbaVlookup = temp
'These lines will be rund if variable layout is not equal to h
Else
'Save the number of rows the user has entered this User Defined Function in
Lrow = Range(Application.Caller.Address).Rows.Count
'Iterate through empty cells and save nothing to them in order to avoid an error being displayed
For r = UBound(temp) To Lrow
temp(UBound(temp)) = ""
ReDim Preserve temp(UBound(temp) + 1)
Next r
'Decrease the size of array variable temp with 1
ReDim Preserve temp(UBound(temp) - 1)
'Return temp variable to worksheet with values rearranged vertically
vbaVlookup = Application.Transpose(temp)
End If
End Function
```

- After that, if you start to write the function in
**Cell C14**, the function will show up like other excel functions.

- Then write the below formula in
**Cell C14**.

`=vbaVlookup(B14,B5:B11,2)`

- Finally, here we have multiple hobbies of Emily as below.

**8. Get Multiple Values in Single Excel Cell**

Till now, we have received multiple values listed vertically in different cells. However, now, we will show multiple values joined in a single cell. Here, we will use **the TEXTJOIN function** along with the **FILTER** function to get the joined multiple values.

**Steps:**

- First, type the below formula in
**Cell C14**.

`=TEXTJOIN(",",TRUE, FILTER(C5:C11, B5:B11=B14))`

- As a result, all the hobbies of Emily are presented horizontally in a single cell.

Here, the **TEXTJOIN** function concatenates the list of hobbies using commas.

**Conclusion**

In the above article, I have tried to discuss the methods elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.