Learn how to analyze data with VLOOKUP multiple criteria and multiple results in Excel. Today, we will look at formulas for VLOOKUP multiple criteria and VLOOKUP multiple results using the features of the VLOOKUP function, XLOOKUP function, and FILTER function.
Excel VLOOKUP Multiple Results
Suppose the data to be analyzed in Excel is shown below. When a seller’s name is recorded multiple times, we need to find each seller’s sales quantity. Let’s look at how this can be solved with Excel functions.
We want to retrieve multiple results using the Excel VLOOKUP function. When the target is John, one of the sellers, the formula used is as follows.
G4 cell =VLOOKUP(G3,B3:D13,3,0)
John, used as the lookup value, appears in three records in the sales data above. The Excel VLOOKUP function retrieved only one value: the value located at the top of the data.
Excel FILTER Function (Multiple Results)
The lookup value above matches three records in the sales data. To retrieve each corresponding sales record, we will use the Excel FILTER function to return VLOOKUP multiple results.
Formula for VLOOKUP multiple results = FILTER function
The basic syntax of the Excel FILTER function is as follows.
=FILTER (column range to return, column range containing the lookup value = lookup value)
The FILTER function used to retrieve all of John’s sales quantities from the sales data is as follows.
G9 cell =FILTER(D2:D13,B2:B13=G8)
The sales quantity in column D, which is the value to return, is placed first. Next, the seller name in column B, which contains the lookup value, is compared with the lookup value to return VLOOKUP multiple results with the FILTER function.
The formula for finding multiple results in Excel is the FILTER function. Keeping this in mind can help you overcome the limitations of VLOOKUP.
Excel VLOOKUP Multiple Criteria
Let’s look at how to find the value you want using VLOOKUP multiple criteria. To retrieve a value that meets multiple criteria, the logical structure of those criteria must exactly match the value being returned.
Let’s use an example with two criteria from the sales table data: the seller’s name and the product sold.
The first step is to create a combined value for the multiple criteria in the sales table. Enter the seller name from column B and the product sold from column C into column D using the following formula.
D3 cell = B3&C3
Next, use the combined value in column D as the lookup value for VLOOKUP and return the multiple-criteria result from the sales quantity in column E.
H9 cell =VLOOKUP(H3&H4,D2:E13,2,0)
For selecting each individual criterion, we recommend using Excel’s list feature.
Excel XLOOKUP Function (VLOOKUP Multiple Criteria)
To apply multiple criteria, you do not necessarily need to modify the data as in the VLOOKUP multiple-criteria example above. The main types of LOOKUP functions can be summarized as the following four.
- VLOOKUP & HLOOKUP
- XLOOKUP
- INDEX MATCH
- Set a PivotTable Range
- Count Unique Values in a PivotTable Excluding Duplicates
- How to Fix Excel Errors
The first function that can replace VLOOKUP is the XLOOKUP function.
Formula for VLOOKUP multiple criteria = XLOOKUP function
Let’s briefly look at the basic syntax of Excel XLOOKUP.
=XLOOKUP(lookup value, lookup column, return column)
It is widely used in Excel 365. In most cases, X functions go beyond the capabilities of traditional Excel functions.
It is faster than VLOOKUP and can retrieve a single value regardless of where the lookup value is located. VLOOKUP can retrieve only data to the right of the lookup value, but XLOOKUP has no restriction on retrieving data to the left or right.
Let’s look at multiple criteria with the Excel XLOOKUP function. The XLOOKUP formula used to retrieve a value that meets multiple criteria is as follows.
H9 cell =XLOOKUP(1,(B3:B13=H3)*(C3:C13=H4),E3:E13)
The formula uses “*” to enter the logic for two criteria and return the cell value that meets both criteria.
Conclusion
We looked at how to find VLOOKUP multiple criteria and multiple results in Excel. Along the way, we explained the FILTER and XLOOKUP functions with examples. We will also examine all functions related to LOOKUP functions in detail, including these two functions. To apply the functions discussed today fully, please see our article on absolute references in Excel formulas.