Power Spreadsheets

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

Excel XLookup Return All Matches in 3 Easy Steps (+ Free Easy-To-Adjust Excel Workbook Example)

In this Excel XLookup Return All Matches Tutorial, you learn how to create an Excel XLookup return all matches formula.

The Excel XLookup return all matches formula template/structure you learn below relies on the FILTER function. FILTER is available in Excel 2021 and later (including Excel 365).

This Excel XLookup Return All Matches Tutorial is accompanied by an Excel workbook with the data and formulas I use. Get this example workbook (for free) by clicking the button below.


Get immediate free access to the Excel XLookup return all matches example workbook

Related Excel Training Materials and Resources

The following Excel VLookup Tutorials may help you better understand the differences between the XLOOKUP and VLOOKUP functions (XLOOKUP vs. VLOOKUP).

This Excel XLookup Return All Matches Tutorial is part of a more comprehensive series of Excel XLOOKUP Tutorials.

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

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

If you want to learn 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 help with Excel tasks/projects, you may be interested in working with me: Click here to learn more about working with me.


Get immediate free access to the Excel XLookup return all matches example workbook

Excel XLookup Return All Matches Formula Template/Structure

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup-all-matches/
=FILTER(ColumnOrRowWithValuesToReturn,ColumnOrRowWhereYouSearch=LookupValue)


Get immediate free access to the Excel XLookup return all matches example workbook

Step-by-Step Process to Create an Excel XLookup Return All Matches Formula

(1) Call the FILTER function.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup-all-matches/
=FILTER(...)

(2) Use the first argument of the FILTER function (array) to specify the return array. This is the column (when doing a VLookup) or row (when doing an HLookup) with the value to return.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup-all-matches/
=FILTER(ColumnOrRowWithValuesToReturn,...)

(3) Set the second argument of the FILTER function (include) to an expression with the following 3 items:

  1. The column (when doing a VLookup) or row (when doing an HLookup) where you search for the lookup value (the lookup array).
  2. The equal to comparison operator (=).
  3. The value you search for in the column (when doing a VLookup) or row (when doing an HLookup) you search in (the lookup value).

Ensure the number of rows (when doing a VLookup) or columns (when doing an HLookup) of the lookup array (you specify in this step #3) is the same as that of the return array (you specified in step #2).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup-all-matches/
=FILTER(ColumnOrRowWithValuesToReturn,ColumnOrRowWhereYouSearch=LookupValue)


Get immediate free access to the Excel XLookup return all matches example workbook

How (and Why) the XLookup Return All Matches Formula Works

The XLOOKUP function:

  1. Searches a range or array for a match; and
  2. Returns the corresponding item(s) from a second range or array.

XLOOKUP returns the item(s) corresponding to a single (the first) match.

You can (however) work with the FILTER function to return all matches (create an Excel XLookup return all matches formula). The FILTER function:

  • Filters an array based on the criteria (for example, a lookup value) you specify; and
  • Returns an array with all applicable (filtered) matches.


Get immediate free access to the Excel XLookup return all matches example workbook

Excel XLookup Return All Matches Example Formula

This Excel XLookup Return All Matches Tutorial is accompanied by an Excel workbook with the data and formulas I use. Get this example workbook (for free) by clicking the button below.


Get immediate free access to the Excel XLookup return all matches example workbook

Excel XLookup Return All Matches Example Worksheet

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

(1) Table 1 (cells A6 to H26).

The table:

  • With the data.
  • Where I search with the XLookup return all matches example formula.

(2) Lookup value (cells J6 and K6).

The lookup value (Salesperson 8) is stored in cell K6.

(3) XLookup return all matches example formula (cells J8 to K13).

  • Cell J9 stores the XLookup return all matches example formula.
  • Cells J9 to J13 display the results. This is because:
    • The array returned by the XLookup return all matches example formula has 5 items; and
    • Results spill from cell J9 into cells J10 to J13.
  • Cell K9 displays the XLookup return all matches example formula I enter in cell J9.
Example worksheet for Excel XLookup return all matches

Excel XLookup Return All Matches Example Formula

The XLookup return all matches example formula stored in cell J9 is as follows:

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-xlookup-all-matches/
=FILTER(H7:H26,A7:A26=K6)

The lookup value (stored in cell K6) is “Salesperson 8”.

This value is found in the following rows of the column I search in with the XLookup return all matches example formula (column A, cells A7 to A26):

  • Row 8 (cell A8).
  • Row 12 (cell A12).
  • Row 17 (cell A17).
  • Row 22 (cell A22).
  • Row 25 (cell A25).
Excel XLookup return all matches example formula


Get immediate free access to the Excel XLookup return all matches example workbook

Download the Excel XLookup Return All Matches Example Workbook

This Excel XLookup Return All Matches Tutorial is accompanied by an Excel workbook with the data and formulas I use. Get this example workbook (for free) by clicking the button below.


Get immediate free access to the Excel XLookup return all matches example workbook

Related Excel Training Materials and Resources

The following Excel VLookup Tutorials may help you better understand the differences between the XLOOKUP and VLOOKUP functions (XLOOKUP vs. VLOOKUP).

This Excel XLookup Return All Matches Tutorial is part of a more comprehensive series of Excel XLOOKUP Tutorials.

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

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

If you want to learn 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 help with Excel tasks/projects, you may be interested in working with me: Click here to learn more about working with me.


Get immediate free access to the Excel XLookup return all matches example workbook

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