How to Find Multiple Values in Excel (8 Quick Methods)

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.


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).

Use Find and Replace Tool to Get Multiple Values in Excel

  • 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.

Use Find and Replace Tool to Get Multiple Values in Excel

  • 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.

Excel Filter Option to Find Multiple Values

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

Excel Filter Option to Find Multiple Values

  • 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).

Apply Advanced Filter Option to Return Multiple Values

  • Next, go to Data > Sort & Filter > Advanced.

Apply Advanced Filter Option to Return Multiple Values

  • As a consequence, the Advanced Filter window will show up. Now, set the List range (Dataset range) and Criteria range and click OK.

Apply Advanced Filter Option to Return Multiple Values

  • 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).

Return Multiple Values by Using Excel Defined Table

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

Return Multiple Values by Using Excel Defined Table

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

Return Multiple Values by Using Excel Defined Table

  • 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:


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)

Insert FILTER Function to Find Multiple Values

  • 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)

Search Multiple Values with INDEX Function in Excel

  • As a result, we received the below result.

Search Multiple Values with INDEX Function in Excel

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

Search Multiple Values with INDEX Function in Excel

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

Search Multiple Values with INDEX Function in Excel

🔎 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.

Search Multiple Values with INDEX Function in Excel

  • 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.

Search Multiple Values with INDEX Function in Excel

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.

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

  • 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.

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

  • 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

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

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

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

  • 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))

Get Multiple Values in Single Excel Cell

  • 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.


Further Readings

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo