Intro
The VLOOKUP function in Excel is a powerful tool for searching and retrieving data from a table. However, by default, it only returns the first match it encounters, which can be limiting if you need to work with multiple matches. Fortunately, there are several ways to VLOOKUP with multiple matches in Excel, and we'll explore five of them in this article.
Understanding the VLOOKUP Function
Before we dive into the methods, let's quickly review how the VLOOKUP function works. The syntax for VLOOKUP is as follows:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value: The value you want to search for.
- table_array: The range of cells that contains the data you want to search.
- col_index_num: The column number that contains the value you want to return.
- [range_lookup]: Optional. If set to FALSE, the function will only return an exact match.
Method 1: Using the INDEX-MATCH Function
One of the most popular alternatives to VLOOKUP is the INDEX-MATCH function combination. This method allows you to return multiple matches by using the MATCH function to find the relative position of the lookup value, and then using the INDEX function to return the corresponding values.
How to Use INDEX-MATCH for Multiple Matches
Assuming you have a table with the following structure:
| Employee ID | Name | Department |
|---|---|---|
| 101 | John Smith | Sales |
| 102 | Jane Doe | Marketing |
| 103 | John Smith | IT |
To return all the departments for John Smith, you can use the following formula:
=INDEX(C:C, MATCH(1, (A:A="101") * (B:B="John Smith"), 0))
This formula will return the first match, which is "Sales". To return all the matches, you can use an array formula:
=INDEX(C:C, SMALL(IF((A:A="101") * (B:B="John Smith"), ROW(A:A), ""), ROW(A1)))
This formula will return both "Sales" and "IT".
Method 2: Using the FILTER Function (Excel 365 and later)
If you have Excel 365 or later, you can use the FILTER function to return multiple matches. This function allows you to filter a range of cells based on a condition.
How to Use the FILTER Function for Multiple Matches
Using the same table as above, you can use the following formula to return all the departments for John Smith:
=FILTER(C:C, (A:A="101") * (B:B="John Smith"))
This formula will return both "Sales" and "IT".
Method 3: Using the VLOOKUP Function with an Array Formula
You can also use the VLOOKUP function with an array formula to return multiple matches. This method involves using the VLOOKUP function to return an array of values, which can then be filtered to return only the matches.
How to Use VLOOKUP with an Array Formula for Multiple Matches
Using the same table as above, you can use the following formula to return all the departments for John Smith:
=VLOOKUP("101", A:C, 3, FALSE) * (B:B="John Smith")
This formula will return an array of values, which can then be filtered to return only the matches.
Method 4: Using the XLOOKUP Function (Excel 365 and later)
If you have Excel 365 or later, you can use the XLOOKUP function, which is a more powerful and flexible alternative to VLOOKUP. This function allows you to return multiple matches by using the IF function to filter the results.
How to Use the XLOOKUP Function for Multiple Matches
Using the same table as above, you can use the following formula to return all the departments for John Smith:
=XLOOKUP("101", A:C, 3, "", IF(B:B="John Smith"))
This formula will return both "Sales" and "IT".
Method 5: Using Power Query
Finally, you can use Power Query to return multiple matches. This method involves creating a query that filters the data based on the lookup value.
How to Use Power Query for Multiple Matches
Using the same table as above, you can create a query that filters the data based on the lookup value:
- Go to the "Data" tab and click on "From Table/Range".
- Select the table and click "OK".
- In the Power Query Editor, click on "Add Column" and then "Custom Column".
- Enter the following formula:
= Table.SelectRows(#"Filtered Rows", each [Employee ID] = "101" and [Name] = "John Smith")
This formula will return both "Sales" and "IT".
Gallery of Excel VLOOKUP with Multiple Matches
Excel VLOOKUP with Multiple Matches Image Gallery
In conclusion, there are several ways to VLOOKUP with multiple matches in Excel, and the method you choose will depend on your specific needs and version of Excel. By using the INDEX-MATCH function, FILTER function, VLOOKUP with an array formula, XLOOKUP function, or Power Query, you can return multiple matches and take your data analysis to the next level.