Short Answer: It Can’t. But don’t worry about it because there’s a better function to get what you want. Yes, all matches.
Take an example of data where you have salespersons, products, and an amount in each entry. And you have multiple entries for all the salespersons.
If you want to get all the entries for a particular salesperson from this data, it’s not possible to have them using XLOOKUP because XLOOKUP cannot get you all the matches or the salesperson entries from the data.
As I said, there’s a better way (even two ways) that you can use to get all the matches in return for a value. And that’s by using the Filter Function and Power Query.
In this tutorial, I’m going to show you both methods in detail, step by step.
Return All the Matches with FILTER Instead of XLOOKUP
In the below example, where I have a value in cell E1, and when I change this value filter returns me all the matches from the main table.
And the best part is, this method is quite simple to use, and you don’t need to write a complex formula.
When you enter the FILTER function, you can see you have three different arguments to define, and in the first argument, you need to refer to the main table that is in the range A1:C121.
In my example, I have an Excel table applied to this range, and the name of this table is SalesData. When referring to this table, you can use the name of the table, as you can see in my formula.
After that, in the second argument, which is “Include”, you need to refer to the salesperson column because this is the column where you have all the salesperson names.
Along with that, you also need to write a condition that allows you to get all the entries for a particular salesperson. So, for this, you need to enter the “=” and then refer to the cell E2, where you have the salesperson’s name.
The third argument in the filter function allows you to have a particular value if there is no match for the value you are looking for.
You can specify anything here. In my case, I am going to specify a blank value, but you can specify any value, even a meaningful message like “No Value Found” using double quotation marks.
At this point, your formula is complete; all you need to do is just hit Enter to get all the matches for the value that you have in cell E1.
In my example, I have the value “Rachel Adams”, and that is why I have all the matching entries for the salesperson “Rachel Adams”.
To make this LOOKUP formula more dynamic, you can create a drop-down list that will allow you to change the name of the salesperson.
For this, you need to use the keyboard shortcut Alt, A, V, V, and this will open the dropdown dialog box.
From the data validation dialog box, click on the “Allow” drop-down and select List, and from the source input bar, click on the small upwards arrow and then select the salesperson column from the first value to the last.
Now, click OK to create a new dropdown list on cell E1.
Use Power Query to Get All the Matches in Return
In the second method, we’re going to use Power Query, which is as powerful as the first method that we have used.
ADD GIF
In Power Query, you need to use the same mechanism. You need to have one cell where you’re going to add your value for which you want to get all matches in return.
Step 1 – Create a Query for the Salesperson’s Name to use as Lookup Value
As I said, first, you need to have one cell where you’re going to add the value (name of the salesperson) that you want to look up. In my case, I’m going to use cell G1. You can use any cell in your data.
After that, right-click on the cell and go to the option that says, “Get data from Table/Range”.
When you click on this option, it will ask you to convert your single cell into an Excel table and make sure to not to tick mark the option “my table has headers” and then click OK.
Within a second, it will load your single cell value into the Power Query editor. And the next thing that you need to do is, right-click on the cell and then click on the option drill down.
When you click on the drill down, it converts your single value into a list.
Now, the next thing is to rename your query. If you look at the left side of your window, you’ll find the query pane, from there right click on the query name and select the option rename.
You can use any name, in my example, I’m going to use the name “Filter Value”.
At this point, your query is renamed, and the next step is to create a connection with this query. For this, go to the home tab and then from the close and load drop-down, click on the “Close and load to”.
And then, when you get the load data dialog box, click on only create connection and then click OK.
Here is one more small tweak that you need to make. When you select the small table where you have the salesperson’s name, go to the “Table Design” tab and then untick the header row this will remove the header from the table.
And after that, just by dragging and dropping, move the cell with the salesperson’s name from the G2 to G1.
Step 2 – Load Main Table to the Power Query Editor
And now you need to load your main data into the Power Query editor so that you can apply a filter to that data and then get only those entries for which you have the value in the cell G1.
Click on any of the cells on the table, right click, and then go to the option “Get data from Table/Range”. This will ask you to convert your data into an Excel table (if you have not converted it already.
Click OK to convert it into an Excel table, and then it will take just a second to load your data into the Power Query editor.
At this point, you have your data into the Power Query editor and the next thing is to apply a filter to the salesperson column.
For this, just click on the downward arrow that you have on the salesperson column and select any of the salesperson’s name and then click OK.
Step 3 – Create a Dynamic Filter to Get all the Matches
The next thing is to make this filtration, the filter that we have applied, a dynamic one. That means connecting this filter to the value that we have in cell G1 in the worksheet.
What I’m saying here is to connect to your filter with the query that you have already created, “Filter Value”.
If you look at the formula bar, you can see I have the value James Carter which is a hard value and this is the value for which I have applied the filter on the salesperson column.
But you have to replace this hard value with the name “FilterValue which is a query where you have the salesperson’s name from the worksheet.
We are doing this because this will make your main query dynamic, and it will get the value from the cell G1 from the worksheet and then apply the filter and return new data.
This will all make sense in a few more seconds.
Step 4 – Load Data back to the Worksheet
Before you load this data back to your worksheet, you need to make a small change, that is the renaming the query.
Right click on the query that you have on the left side of the Window and give it all relevant name. In my example, I am going to use the name “FilterData”.
Now, everything is set up and you need to load your data back to your worksheet. For this, you need to go to the home tab and then go to the “Close and Load” drop-down and from there click on “Close and load to”.
After that, from the drop-down click on the “Existing worksheet” and then select a cell where you want to load this table with the filter. And then click OK.
You can see here that the salesperson’s name that you have in cell G1 is James Carter, and that is for which you have data in your new table.
To test this, you can change the name from cell G1 and then right-click on your new table and click on the refresh option.
This will instantly refresh your table and return all the matches from the main table for that salesperson.
Step 5 – Make the Refresh Automatic Table
You can also make your table refresh automatically instead of changing the value every time, instead of right-clicking and then using the Refresh option.
For this, you can use a small VBA code that will automatically refresh the table whenever you change the value in cell G1.
Right-click on the worksheet tab and click on View Code.
This will open the Visual Basic editor for your worksheet, and this is where you need to insert your VBA code. And once you insert your VBA code you need to make two small changes.
Private Sub Worksheet_Change(ByVal Target As Range)
' Check if the changed cell is A2
If Not Intersect(Target, Me.Range("G1")) Is Nothing Then
' Refresh the table named "SalesTable"
Me.ListObjects("FilterTable").Refresh
End If
End SubFirst is the name of the table and second is the cell address where you have the salesperson’s name. Once you make the changes close the visual basic editor.
That’s it.
Your dynamic table is ready to use, and it will return all the matches from the main table whenever you enter or change the name of the salesperson in the cell G1.
You can use the same mechanism for any column. Let’s say if you want to use the product instead of a salesperson, you can do that.
ADD GIF
Step 6 – (Optional) Create a Drop-Down List for the Salesperson
Just like the first method, you can create a drop-down list here also.
You just need to use the keyboard shortcut Alt, A, V, V, and then select the salesperson’s column in the source, and then click OK to create your drop-down list.
Whenever you select any salesperson’s name from the drop-down down your query will extract all the matching entries from the main table, and the VBA code will refresh the table to show you the data.
Even you can create a pivot table from this data, or you can you use the GROUPBY function to create a summary table further.