When conducting data analysis in Microsoft Excel**,** you may need to obtain all matching data for a certain ID, username, contact information, or another unique identifier, you may run into problems. The article illustrates how to use Excel to lookup multiple values in Excel based on one or more conditions and return multiple results in a column, row, or single cell. I’ll try to explain the concepts as best I can so that a beginner may comprehend them and apply them to comparable problems.

## 1. Using Array Formula to Lookup Multiple Values in Excel

The Excel **VLOOKUP Function **springs to mind as an immediate answer, but the difficulty is that it can only return a single match.

To execute the tasks, we may utilize an array formula using the following functions.

**IF**– It outputs one value if the condition is satisfied and another value if the condition is not satisfied.**SMALL**– It returns the array’s lowest value.**INDEX**– Gives an array element depending on the rows and columns provided by you.**ROW**– It provides you with the row number.**COLUMN**– It gives you the number of the column.**IFERROR**– detect errors.

A few instances of these formulas can be seen below.

### 1.1. Lookup Multiple Values in A Row

Let’s say, we have a few names of executives who run multiple companies in column **B**. We have shown the company names in column **C**. Our objective is to compile a list of all businesses run by a specific person. Please follow these steps to have it completed.

**Step 1:**

- In an empty row, provide a list of unique names. The names are entered in cells
**B13:B15**in this example.

**Step 2:**

**Enter**the following formula in the cell

`=IFERROR(INDEX($C$5:$C$10, SMALL(IF($B15=$B$5:$B$10, ROW($C$5:$C$10)-4, " "), COLUMN()-2)), " ")`

- To make sure as an array condition, press
**Ctrl**+**Shift**+**Enter**simultaneously

**Step 3:**

- Press
**Enter**and use**AutoFill**to see the results.

And the Final result is this.

### 1.2. Lookup Multiple Values in A column in Excel

For a reason, If you want to return multiple values in Columns instead of rows, as shown in the

Below the screenshot modify the formulas as follows in the steps below.

**Step 1:**

- Enter a list of unique names in some empty row, In this example, the names are input in cells
**E4:G4** - Type the following formula in cell
**E5**

`=IFERROR(INDEX($C$5:$C$10, SMALL(IF(E$4=$B$5:$B$10, ROW($C$5:$C$10)-4, " "), ROW()-4)), " ")`

- For an array condition, press
**Ctrl**+**Shift**+**Enter**.

**Step 2:**

- Finally, Press
**Enter**and fill the required cell with the**AutoFill**handle tool.

Here is the final results.

**Note**. For the formula to get copied correctly to other rows, mind the lookup value references, absolute column, and relative row, like **$E4.**

**Read More: ****Different Types of Lookup to Apply in Excel**

## 2. Searching for Multiple Values in Excel Based on Multiple Criteria

You already know how to lookup multiple values in Excel based on a single criterion. What if you want multiple matches based on two or more criteria? Taking an example, you have a data set of **Amazon **best-selling products under specific categories in different columns. Now, you are looking to get a product under a certain category.

We will use the following array argument to do it.

**IFERROR(INDEX( return_range, SMALL(IF(1=((–(lookup_value1=lookup_range1)) * ( –(lookup_value2=lookup_range2))), ROW(return_range)-m,””), ROW()-n)),””)**

Where,

**Lookup_value1 **is the first lookup value in cell **F5**

**Lookup_value2 **is the second lookup value in cell **G5**

**Lookup_range1 **is the range where lookup_value1 will be searched (**B5:B10**)

**Lookup_range2 **is the range where lookup_value2 will be searched (**C5:C10**)

**Return_range **is the range from where the result will be given.

**m **is the row number of the first cell in the return range minus **1**.

**n **is the row number of the first formula cell minus **1**.

### 2.1. Lookup Multiple Matches in A Column

As you are familiar with the array argument, you may simply use the formulas presented in the previous two examples to check multiple criteria, as shown in the steps below.

**Step 1:**

- In cell
**H5**, type the following formula,

`=IFERROR(INDEX($D$5:$D$10, SMALL(IF(1=((--($F$5=$B$5:$B$10)) * (--($G$5=$C$5:$C$10))), ROW($D$5:$D$10)-4,""), ROW()-4)),"")`

- Press
**Ctrl**+**Shift**+**Enter**simultaneously to apply the formula

As a result, it will show the value like in the below screenshot.

**Step 2:**

- Apply the same formula to the rest of the cells.

**Note.** Because our return range and formula range both begin in row 5, both n and m are equal to “4” in the example above. These may be different numbers in your worksheets.

**Read More: ****How to Lookup with Multiple Criteria in Excel**

### 2.2. Lookup Multiple Matches in A Row

Similar to the previous method, you may prefer the horizontal layout where results are returned in rows. If you want to pull multiple values based on multiple criteria sets, in this case, follow the steps below.

**Step 1:**

- Firstly, In cell
**D13**, type the following formula,

`=IFERROR(INDEX($D$5:$D$10, SMALL(IF(1=((--($B$13=$B$5:$B$10)) * (--($C$13=$C$5:$C$10))), ROW($D$5:$D$10)-4,""), COLUMN()-3)),"")`

- To make it an array, press
**Ctrl**+**Shift**+**Enter**.

**Step 2:**

- Then, just hit the
**Enter**button and use**AutoFill**to fill in the required cells.

Consequently, it will show multiple results like in the below screenshot.

**Read More: ****How to Lookup Text in Excel **

## 3. Returning Multiple Values in One Cell after Doing Lookup

With a **Microsoft 365 **subscription, Excel now includes a lot more powerful functions and features (such as **XLOOKUP**, **Dynamic Arrays**, **UNIQUE/FILTER** functions, etc.) that weren’t available in previous versions.

If you’re using **Microsoft 365** (formerly known as **Office 365**), the methods described in this section can be used to look up and return multiple values in one cell in Excel.

Below I have a data set where I have the names of the executives in column **B **and the companies, they own in column **C**.

For each person, I want to look up which companies they own in a single set (separated by a comma) In cell **F5**.

To do this, apply the following steps.

**Step 1:**

- Firstly, enter the following formula in cell
**F5**.

`=TEXTJOIN(", ",TRUE,IF(E5=$B$5:$B$10,$C$5:$C$10,""))`

- To input as an array formula, hit
**Ctrl**+**Shift**+**Enter**.

** **

**Step 2:**

- Then, hit
**Enter**to see the results.

**Read More: ****How to Lookup Value from Another Sheet in Excel**

## 4. Applying the FILTER Function to Lookup Multiple Values in Excel

You may use the **FILTER Function** to filter a set of data depending on the criteria you give to seek numerous values.

The **Dynamic Arrays Function** contains this function. The result is an array of data that dynamically flows into a range of cells, starting with the cell where you entered the formula.

The** FILTER function** has the following syntax.

**FILTER(array, include, [if_empty])**

Where,

**Array (required)** – the value range or array that you wish to filter.

**Include (required)** – the criterion provided in the form of a Boolean array (**TRUE** and **FALSE** values). It must have the same height (when data is in columns) or width (when data is in rows) as the array parameter.

**If_empty (optional)** – When no items fit the criterion, this is the value to return.

For starters, let’s look at a few very simple examples to have a better understanding of how an Excel formula for data filtering works.

### 4.1. IF Not Equal

Let’s say, you want to know the company’s names that do not belong to Elon Musk. So, here our lookup value is Elon Musk in **F4**. To do this, we will apply the following **FILTER Function**.

**Step 1:**

- In cell
**F6**, input the following formula of the**FILTER Function**.

`=FILTER(C5:C10,B5:B10<>F4)`

- To make it an array, press
**Ctrl**+**Shift**+**Enter**.

**Step 2:**

- Then, press
**Enter.** - Use the
**AutoFill**Handle Tool to fill the required field.

Therefore, you will obtain the results as shown in the above screenshot.

### 4.2. IF Equal

Similarly, if you want to know the companies’ names that belong to Elon Musk, follow the steps below.

**Step 1:**

- Type the following formula in cell
**F6**,

`=FILTER(C5:C10,B5:B10=F4)`

- Hit
**Ctrl**+**Shift**+**Enter**at the same time.

**Step 2:**

- Then, press
**Enter**to find the matches. - Apply the
**AutoFill**Handle Tool to fill the cells.

### 4.3. IF Less Than

In the below screenshot, a data set of top billionaires’ net worth is shown. Now, for instance, you want to know who has a net worth of less than **$150B**. To do so, follow these steps.

**Step 1:**

- At first, type the following formula in cell
**F6**,

`=FILTER(C5:C10,B5:B10<F4)`

- To make it an array formula, press
**Ctrl**+**Shift**+**Enter**.

**Step 2:**

- Then, Press
**Enter**. - Finally, apply the
**AutoFill**Handle Tool to fill the cells.

Consequently, you will obtain multiple values as shown in the above screenshot.

### 4.4. IF Greater Than

Similar to the previous method, if you want to know who has a net worth of more than **$150B**, just follow the steps below.

**Step 1:**

- At first, in cell
**F6**, type the following formula,

`=FILTER(C5:C10,B5:B10>F4)`

- Hit
**Ctrl**+**Shift**+**Enter**to make it an array formula.

**Step 2:**

- Then, Press
**Enter**. - Finally, apply the
**AutoFill**Handle Tool to fill the cells.

As a result, you will obtain multiple values as shown in the above screenshot.

**Read More: ****How to Lookup a Table in Excel**

## 5. Applying the VLOOKUP Function to Lookup Multiple Values

In a scenario, you may need to go over your data lists again to check what information is included in each of them and what information is missing from one of them. For example, we want to look up which actors have participated in a particular event. To do this task, we will use the **VLOOKUP Function**.

The syntax of the **VLOOKUP Function **is as follows.

**=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])**

Where,

**Lookup_value** is the reference value, which can be a text, a numerical string, or a cell whose value you want to reference.

**Table_array **is the whole data table including its whole. As a result, the reference value you’re seeking should be in column 1 of this table, so Excel can proceed to the right and look for the return value.

**Col_index_num **is the number of the column in which the return value is found. This number starts at 1 and increases as the number of columns in your table grows.

**[range_lookup] **is the fourth argument in brackets because it isn’t required for this function to work. In Excel syntax, brackets indicate that an argument is optional. If you don’t fill in this value, Excel defaults to TRUE (or 1), indicating that you’re seeking a close match to your reference value rather than an exact match.

**Note. **For text returns, using **TRUE **as the value is not advised.

Now, Apply the **VLOOKUP Function** with the following steps.

**Step 1:**

- In cell
**E5**, type the following formula,

`=IFERROR(VLOOKUP(B5,C:C,1,FALSE),"Not Attened")`

- Press
**Ctrl**+**Shift**+**Enter**to make it an array.

**Step 2:**

- Press
**Enter**to see the results. - Finally, apply the
**AutoFill**Handle Tool to fill the cells.

In the above screenshot, you can see the list who have attended the event and we put ** “Not Attended”** for who have not attended.

**Read More: ****How to Use VBA Lookup Function in Excel**

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

## Conclusion

To conclude, I hope this article has provided detailed guidance to lookup multiple values in Excel. All of these procedures should be learned and applied to your dataset. Take a look at the practice workbook and put these skills to the test. We’re motivated to keep making tutorials like this because of your valuable support.

If you have any questions – Feel free to ask us. Also, feel free to leave comments in the section below.

We, The** ExcelDemy **Team, are always responsive to your queries.

Stay with us & keep learning.

Hi,

your work is great. have tried to follow your lessons but seems the examples do not fit my need and I am kind of stuck trying to figure out the right formula that will give me correct result.

I would like to know who should be the receiver if the the fruit is given (1st criteria) and the number of pieces (2nd criteria) is known.

mango apple grapes strawberry

paul 1 2 4 5

james 2 3 5 6

kent 3 4 6 7

ralph 4 5 7 8

1st Criteria: fruit = grapes

2nd Criteria: number = 6

Result: name = ??? (kent)

Thanks in advance and more power to you.

Greetings,

According to your query, the ExcelDemy team has created an Excel file with the solution. Please provide your email address here, we will send it to you in no time.

Otherwise, you can just follow the procedures as we have proceeded.

Step 1:

a. Our data set range is B4:F8.

b. The names of the fruits range is C4:F4.

c. Names of people range is B5:B8.

Step 2:

a. In cell C11, create a drop-down list with the range C4:F4.

b. Give a named range for the numbers of each fruit with its name (Ex: Named range = Mango for C5:C8).

c. Create another dropdown list dependant to the cell C11. Use the following formula in the Data Validation box from the Data tab to do this.

Step 3:

a. In cell C15, insert the following formula for Mango.

b. AutoFill the formula with dragging right for three cells for more three fruits.

Step 4:

a. Now, you are done, select any name (Grapes) from the first drop-down list.

b. Then, select the number (6).

c. It will result in the person name (Kent).

Please, provide your further feedback if any queries needed.

Hi, I hope you have seen my email, appreciate if you could help me with my inquiry. TYIA.