Using the VLOOKUP function to find multiple results in Excel
Using the VLOOKUP function to find multiple results in Excel. Have you ever wanted to use VLOOKUP but display multiple matching results? The standard VLOOKUP formula in Excel has a limitation and it will only display the first matching result. But what if you need to see all the matches in one place? In this article, let me show you a simple formula to get all the matching results in a list format.
Using the VLOOKUP function to find results
Example:
We want to search for data in the Geography column based on the Sales Person data given by Husein Augar in the Sales Person column.
You will use VLOOKUP to search for the object: =VLOOKUP(G3,sales,2,0)
Explanation: G3: the object to search for.
sales: the named data range.
2: the column position to return the result.
However, in your data range, there may be multiple matches but the VLOOKUP function only displays the limit of one result. Therefore, the FILTER function will be a useful solution for you.
Using the FILTER function to find multiple results
The FILTER function helps you to return multiple results in a data range.
Syntax:
=FILTER(array,include,[if_emty])
Where: array: the data range to filter (not required to have headers).
include: the condition to filter, located in which column (simultaneously determining both factors).
if_empty: What value to return if there are no results? (not required to enter).
Example:
I have a data table as shown below. And instead of using the VLOOPKUP function to find results, you will use the FILTER function!
=FILTER($B$2:$B$37,$A$2:$A$37=$G$3)
Explanation: $B$2:$B$37: the column of data to return the result.
$A$2:$A$37: the column of data that contains the condition, and the condition is: $G$3.
Using the FILTER VLOOKUP function to return a data range
In addition to returning a result column as above, the FILTER function also returns the data range containing the desired results.
With the following formula: =FILTER(sales,sales[Geography]=”USA”)
Explanation: Sales: the named data range to filter.
Sales[Geography]: the named column of data that contains the condition and the condition is: “USA”.
I hope you successfully carry out this article and don’t forget to rate the article for Buffcom!
