How to Lookup Multiple Values in Excel (10 Ways)

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

• 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

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

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

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

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

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

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

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

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

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