How to Lookup Multiple Values in Excel (10 Ways)

Method 1 – Using an Array Formula to Lookup Multiple Values in Excel

The VLOOKUP Function can only return a single match.

We can use an array formula with one of 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.

When applying an array formula in versions of Excel other than Excel 365, you need to use Ctrl + Shift + Enter instead of Enter.

Case 1.1 – Lookup Multiple Values in A Row

We have a few names of executives who run multiple companies in column B. We’ll compile a list of all businesses (from C) run by a specific person.

Steps:

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

• Enter the following formula in the cell C13.

• Use AutoFill to see the results.

• Here’s the result.

Case 1.2 – Lookup Multiple Values in a Column in Excel

We modified the result table.

Steps:

• Enter a list of unique names in and empty row such as cells E4:G4.
• Apply the following formula in cell E5.

• Press Ctrl + Shift + Enter.

• Fill the other cell with the AutoFill handle tool.

• Here’s the result.

Method 2 – Searching for Multiple Values in Excel Based on Multiple Criteria

We have a data set of Amazon bestselling products under specific categories in different columns. We’ll get a product under a certain category.

We will use the following array formula:

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

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.

Case 2.1 – Look up Multiple Matches in a Column

Steps:

• In cell H5, insert the following formula.

• Press Ctrl + Shift + Enter to apply the formula

• It will show the value in the below screenshot.

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

Case 2.2 – Look up Multiple Matches in a Row

We can modify the table for a horizontal layout.

Steps:

• In cell D13, enter the following formula:

• Press Ctrl + Shift + Enter.

• Use AutoFill to fill in the other cells.

• Here’s the result.

Method 3 – Returning Multiple Values in One Cell After a Lookup

This method uses functions only available in Excel 365 at the time of writing.

We have a data set with the names of the executives in column B and the companies they own in column C. For each person, we want to look up which companies they own in a single list (separated by a comma) in cell F5.

Steps:

• Insert the following formula in cell F5.

• Hit Ctrl + Shift + Enter.

• Here are the results.

Method 4 – Applying the FILTER Function to Lookup Multiple Values in Excel

The FILTER Function is available in Excel 365. It has the following syntax.

FILTER(array, include, [if_empty])

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.

Case 4.1 – IF Not Equal

We’ll fetch the company’s names that do not belong to Elon Musk.

Steps:

• In cell F6, input the following formula:

• Press Enter.

• Use the AutoFill Tool to fill the required field.

Case 4.2 – IF Equal

We’ll get the names of the companies that belong to Elon Musk.

Steps:

• Insert the following formula in cell F6:

• Hit Ctrl + Shift + Enter.

• Apply the AutoFill Handle Tool to fill the cells.

Case 4.3 – IF Less Than

We have a list of net worths and want to fetch names with net worths less than $150B.

Steps:

• Insert the following formula in cell F6:

• Press Ctrl + Shift + Enter.

• Apply the AutoFill tool to fill the cells.

Case 4.4 – IF Greater Than

We’ll fetch net worths greater than $150B.

Steps:

• In cell F6, insert the following formula:

• Hit Ctrl + Shift + Enter.

• Apply the AutoFill tool to fill the cells.

Method 5 – Applying the VLOOKUP Function to Lookup Multiple Values

We have a list of actor names in one column and a list of participants of an event in another. We’ll fetch the list of participating actors by looking up each value from one list in another.

The syntax of the VLOOKUP Function is as follows.

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

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 searches, using TRUE as the last argument is not advised.

Steps:

• In cell E5, insert the following formula:

• Press Ctrl + Shift + Enter.

• Use AutoFill to fill the cells.

Download the Practice Workbook

<< Go Back to Lookup | Formula List | Learn Excel