Power Spreadsheets

Excel and VBA tutorials and training. Learn how to use Microsoft Excel and Visual Basic for Applications now.

Excel VLookup Return Multiple Values with the FILTER Function in 2 Easy Steps (+ Free Easy-To-Adjust Excel Workbook Example)

Excel VLookup Return Multiple Values with the FILTER Function in 2 Easy Steps (+ Free Easy-To-Adjust Excel Workbook Example)In this Excel VLookup to Return Multiple Values (with the FILTER Function) Tutorial, you learn how to:

  • Carry out a VLookup and return multiple values;
  • With the FILTER function.

The VLookup to return multiple values (with the FILTER function) formula template/structure you learn in this Tutorial isn't the only way to carry out a VLookup and return multiple values. You may (also) be interested in the following alternative formula template/structures:

  • Excel VLOOKUP Return Multiple Values with Helper Column.
  • Excel VLookup Return Multiple Values with the INDEX Function.
  • Excel VLookup Return Multiple Values in One Cell Separated by a Comma.

I link to these Tutorials in the Related Excel Training Materials and Resources Section below.

This Excel VLookup to Return Multiple Values (with the FILTER Function) Tutorial is accompanied by an Excel workbook with the data and formulas I use when describing the step-by-step process below. Get this example workbook (for free) by clicking the button below.


Get immediate free access to the Excel VLookup return multiple values (with the FILTER function) workbook example

This Excel VLookup to Return Multiple Values (with the FILTER Function) Tutorial is part of a more comprehensive series of Excel VLookup Tutorials.

My Excel XLOOKUP Tutorial (click here to open) may help you:

  • Better understand and implement the contents below.
  • Better understand the differences between the XLOOKUP and VLOOKUP functions (XLOOKUP vs. VLOOKUP).

You can find more Excel Tutorials in the organized Tutorials Archive: Click here to visit the Archives. The following are some of my most popular Excel Tutorials:

If you want to learn more about Excel essentials, Excel formulas, and similar Excel topics, you may be interested in taking one (or more) Excel Courses: Click here to learn more about these Excel Courses (affiliate link). The following are some of the topics covered in these Excel Courses:

  • Excel essentials and must-know skills.
  • Advanced Excel Formulas.
  • Excel Tables.
  • Pivot Tables.
  • Dashboards.
  • Power Pivot.
  • Power Query.

If you want to start learning how to automate Excel (and save time) by working with macros and VBA, you may be interested in the following Premium Excel Macro and VBA Training Materials:

If you need consulting services, you may want to consider working with ExcelRescue. ExcelRescue is my usual suggestion for people who (like you) may need help with Excel tasks/projects: Click here to visit ExcelRescue (affiliate link).


Get immediate free access to the Excel VLookup return multiple values (with the FILTER function) workbook example

The VLookup Return Multiple Values (with the FILTER Function) Formula Template/Structure

The following is the VLookup return multiple values (with the FILTER function) formula template/structure I explain (step-by-step) in the Sections below.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-values-filter/
=FILTER(ColumnWithValuesToReturn,ColumnYouSearchIn=LookupValue)


Get immediate free access to the Excel VLookup return multiple values (with the FILTER function) workbook example

The Example Before VLookup Return Multiple Values (with the FILTER Function)

This Excel VLookup to Return Multiple Values (with the FILTER Function) Tutorial is accompanied by an Excel workbook with the data and formulas I use when describing the step-by-step process below. Get this example workbook (for free) by clicking the button below.


Get immediate free access to the Excel VLookup return multiple values (with the FILTER function) workbook example

The example worksheet has 3 tables/sections with the following characteristics.

(1) Table 1 (Cells A7 to H27).

The table:

  • With the data.
  • Where you search with the VLookup to return multiple values (with the FILTER function) formula.

Main characteristics:

  • 8 columns (Salesperson, Date, Customer, Product, City, Units, Unit Price, Total Sales).
  • 1 header row (row 7).
  • 20 entries (rows 8 to 27).

(2) Lookup Value (Cells J7 and K7).

  • A label (cell J7); and
  • The criterion/value (lookup value) you look up in Table 1 (cell K7).

(3) Table 2 (Cells J9 to L14).

The table where you:

  • Set up the VLookup return multiple values (with the FILTER function) example formula.
  • Display the results.

This table has the following 3 columns:

  • Match: Cells J10 to J14 store numbers. These numbers:
    • Are included for illustrative purposes only; and
    • Represent the match occurrence out of the multiple matches found by the VLookup return multiple values (with the FILTER function) example formula.
  • Total Sales: Cells K10 to K14:
    • Are currently empty.
    • Will store the VLookup return multiple values (with the FILTER function) example formula.
  • Formula: Cell L10:
    • Currently displays the #N/A error.
    • Will display the VLookup return multiple values (with the FILTER function) example formula.
Example: Data for VLookup to return multiple values (with the FILTER function)


Get immediate free access to the Excel VLookup return multiple values (with the FILTER function) workbook example

Step 1: Set Up the VLookup Test

Create an expression that does the following:

  1. Test whether the values stored in the column you search in (with the VLookup criteria) match the value you search for (the lookup value); and
  2. Return a Boolean value (TRUE or FALSE) for each row (in the column you search in), indicating whether the value stored in the applicable row matches (or not) the lookup value.

To do this inside a VLookup return multiple values (with the FILTER function) formula, work with the equal to comparison operator (=).

Use the following template/structure to set up the VLookup test:

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-values-filter/
ColumnYouSearchIn=LookupValue

The expression template/structure I describe above returns an array of TRUEs and FALSEs.


Get immediate free access to the Excel VLookup return multiple values (with the FILTER function) workbook example

Step 1 Example

In the example worksheet:

  • The column I search in is column A (cells A8 to A27; A8:A27).
  • The lookup value is stored in cell K7.
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-values-filter/
A8:A27=K7

The image below illustrates how this expression returns:

  • TRUE or FALSE for each row (in the column I search in; column A), indicating whether the value stored in the applicable row matches (or not) the lookup value (Salesperson 10).
    • Column I (Step 1 Array) returns TRUE or FALSE, depending on whether the value stored in column A (Salesperson) of the applicable row matches the lookup value (Salesperson 10).
    • Cell J8 displays the formula (using the basic structure of the expression carrying out the VLookup test).
  • An array of TRUEs and FALSEs.
Example: VLookup Test for VLookup to return multiple values (with the FILTER function)

The lookup value (Salesperson 10) is found in 5 rows:

  • Row 8 (cell A8).
  • Row 13 (cell A13).
  • Row 19 (cell A19).
  • Row 20 (cell A20).
  • Row 26 (cell A26).


Get immediate free access to the Excel VLookup return multiple values (with the FILTER function) workbook example

Step 2: Use the FILTER Function to Extract the Value(s) in the Row(s) Where the Lookup Value is Found

The FILTER function filters data based on criteria you specify.

Specify FILTER's arguments as follows to obtain the value(s) the VLookup multiple criteria (with the FILTER function) formula returns:

  1. Array: The column with the value(s) to return.
  2. Include: The expression (returning an array of TRUEs and FALSEs) you created in step #1.
'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-values-filter/
=FILTER(ColumnWithValuesToReturn,ArrayFromStep1)

When considering the array you created in step #1:

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-values-filter/
=FILTER(ColumnWithValuesToReturn,ColumnYouSearchIn=LookupValue)

Consider the following when setting up the FILTER function for the VLookup return multiple values (with the FILTER function) formula:

  • The FILTER function is available in Excel 2021 and later (including Excel 365).
  • The FILTER function is a dynamic array function. This has several consequences, including the following:
    • The FILTER function returns an array.
    • When the array returned by the FILTER function is the final result of the worksheet formula:
      • The array spills (values are returned to neighboring cells).
      • Excel dynamically sizes the output cell range after you enter the applicable worksheet formula.
  • If appropriate, consider using the third (optional) argument of the FILTER function (if_empty) to specify the value to return if the filter returns nothing (the column you search in doesn't contain the lookup value). If you omit the if_empty argument and the filter returns nothing, the FILTER function returns the #CALC! error.
  • The FILTER function returns an error if any value of the include argument:
    • Is an error; or
    • Cannot be converted to a Boolean value.


Get immediate free access to the Excel VLookup return multiple values (with the FILTER function) workbook example

Step 2 Example

The column with the value(s) to return is column H (cells H8 to H27; H8:H27).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-values-filter/
=FILTER(H8:H27,A8:A27=K7)

The image below displays the final results returned by the VLookup return multiple values (with the FILTER function) example formula.

  • Cells K10 to K14 display the values returned by the VLookup return multiple values (with the FILTER function) example formula, as follows:
    • Cell K10 returns the first match, in row 8 (1,399.98, in cell H8).
    • Cell K11 returns the second match, in row 13 (10,999.89, in cell H13).
    • Cell K12 returns the third match, in row 19 (27,199.32, in cell H19).
    • Cell K13 returns the fourth match, in row 20 (29,599.63, in cell H20).
    • Cell K14 returns the fifth match, in row 26 (22,499.55, in cell H26).
  • Cell L10 displays the VLookup return multiple values (with the FILTER function) example formula, as entered in cell K10. In this example:
    • The FILTER function returns 5 values.
    • There's spilling. Values are returned to neighboring cells (cells K11 to K14).
Example: VLookup to return multiple values with the FILTER function


Get immediate free access to the Excel VLookup return multiple values (with the FILTER function) workbook example

Download the VLookup Return Multiple Values (with the FILTER Function) Example Workbook

This Excel VLookup to Return Multiple Values (with the FILTER Function) Tutorial is accompanied by an Excel workbook with the data and formulas I use when describing the step-by-step process above. Get this example workbook (for free) by clicking the button below.


Get immediate free access to the Excel VLookup return multiple values (with the FILTER function) workbook example

This Excel VLookup to Return Multiple Values (with the FILTER Function) Tutorial is part of a more comprehensive series of Excel VLookup Tutorials.

My Excel XLOOKUP Tutorial (click here to open) may help you:

  • Better understand and implement the contents above.
  • Better understand the differences between the XLOOKUP and VLOOKUP functions (XLOOKUP vs. VLOOKUP).

You can find more Excel Tutorials in the organized Tutorials Archive: Click here to visit the Archives. The following are some of my most popular Excel Tutorials:

If you want to learn more about Excel essentials, Excel formulas, and similar Excel topics, you may be interested in taking one (or more) Excel Courses: Click here to learn more about these Excel Courses (affiliate link). The following are some of the topics covered in these Excel Courses:

  • Excel essentials and must-know skills.
  • Advanced Excel Formulas.
  • Excel Tables.
  • Pivot Tables.
  • Dashboards.
  • Power Pivot.
  • Power Query.

If you want to start learning how to automate Excel (and save time) by working with macros and VBA, you may be interested in the following Premium Excel Macro and VBA Training Materials:

If you need consulting services, you may want to consider working with ExcelRescue. ExcelRescue is my usual suggestion for people who (like you) may need help with Excel tasks/projects: Click here to visit ExcelRescue (affiliate link).

Subscribe
Notify of
guest
1 Comment
Most Voted
Newest Oldest
Inline Feedbacks
View all comments

I publish a lot of Tutorials and Training Resources about Microsoft Excel and VBA. Here are some of my most popular Excel Training Resources:

  1. Free Excel VBA Email Course
  2. Excel Macro Tutorial for Beginners
  3. Excel Power Query (Get and Transform) Tutorial for Beginners
  4. Excel Keyboard Shortcut Cheat Sheet
  5. Excel Resources
Terms and Conditions

Privacy Policy

Limit of Liability and Disclaimer of Warranty

Affiliate Disclosure