Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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 concept as best I can so that a beginner may comprehend them and apply them to comparable problems.  


Download Practice Workbook

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


10 Suitable Ways to Lookup Multiple Values in Excel

1. Use 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.

Use Array Formula to Lookup Multiple Values in Excel

Step 2:

  • Enter the following formula in 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

Use Array Formula to Lookup Multiple Values in Excel

Step 3:

  • Press Enter and use the AutoFill to see the results.

Use Array Formula to Lookup Multiple Values in Excel

And the Final result is this.

Use Array Formula to Lookup Multiple Values in Excel

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.

Use Array Formula to Lookup Multiple Values in Excel

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.

Use Array Formula to Lookup Multiple Values in Excel

Step 2:

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

Use Array Formula to Lookup Multiple Values in Excel

Here is the final results.

Use Array Formula to Lookup Multiple Values in Excel

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: How to Lookup Value from Another Sheet in Excel (3 Easy Methods)


2. Lookup 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 have done 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.

Lookup Multiple Values in Excel Based on Multiple Criteria

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

Lookup Multiple Values in Excel Based on Multiple Criteria

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

Lookup Multiple Values in Excel Based on Multiple Criteria

Step 2:

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

Lookup Multiple Values in Excel Based on Multiple Criteria

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 Use LOOKUP Function in Excel (4 Suitable Examples)

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.

Lookup Multiple Values in Excel Based on Multiple Criteria

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 array, press Ctrl + Shift + Enter.

Lookup Multiple Values in Excel Based on Multiple Criteria

Step 2:

  • Then, just hit the Enter button and use AutoFill to fill the required cells.

Lookup Multiple Values in Excel Based on Multiple Criteria

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

Lookup Multiple Values in Excel Based on Multiple Criteria

Read More: How to Lookup with Multiple Criteria in Excel (Both AND or OR Type)


3. Lookup and Return Multiple Values in One Cell

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

Lookup and Return Multiple Values in One Cell

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.

Lookup and Return Multiple Values in One Cell

Step 2:

  • Then, hit Enter to see the results.

Lookup and Return Multiple Values in One Cell

Read More: 7 Types of Lookup You Can Use in Excel


4. Apply 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.

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

Apply Filter Function

Step 2:

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

Apply Filter Function

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 these steps below.

Apply Filter Function

Step 1:

  • Type the following formula in cell F6,
=FILTER(C5:C10,B5:B10=F4)
  • Hit Ctrl + Shift + Enter at same time.

Apply Filter Function

Step 2:

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

Apply Filter Function

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 the net worth of less than $150B. To do so, follow these steps.

Apply Filter Function

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.

Apply Filter Function

Step 2:

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

Apply Filter Function

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

4.4 IF Greater Than

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

Apply Filter Function

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.

Apply Filter Function

Step 2:

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

Apply Filter Function

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

Read More: How to Lookup a Table in Excel (8 Methods)


5. Apply 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.

Apply the VLOOKUP Function to Lookup Multiple Values in Excel

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 is 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 array.

Apply the VLOOKUP Function to Lookup Multiple Values in Excel

Step 2:

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

Apply the VLOOKUP Function to Lookup Multiple Values in Excel

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: Excel LOOKUP vs VLOOKUP: With 3 Examples


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.


Related Articles

Bhubon Costa

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

3 Comments
  1. 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.

      =INDIRECT($C$11)

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

      =INDEX($B$5:$F$8,IF(IF($C$11=C4,MATCH(C4,$B$4:$F$4,0)," ")=(MATCH($C$11,$B$4:$F$4,0)),MATCH($D$11,C5:C8,0), " "),1)

      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.

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

Leave a reply

ExcelDemy
Logo