Table of Contents
Google Sheets is a powerful spreadsheet software that can be used to efficiently manage and analyze data. One of its useful features is the VLOOKUP function, which allows users to search for a specific value in a table and return the corresponding value from a different column. This function can be particularly helpful in finding and organizing data, as it eliminates the need for manual searching and sorting. By using VLOOKUP in Google Sheets, users can easily return all matches for a specific search term, making data retrieval and analysis faster and more accurate. This feature is especially useful for large datasets and complex data analysis tasks, making Google Sheets a valuable tool for businesses, organizations, and individuals alike.
Google Sheets: Use VLOOKUP to Return All Matches
By default, the VLOOKUP function in Google Sheets looks up some value in a range and returns a corresponding value only for the first match.
However, you can use the following syntax with to look up some value in a range and return corresponding values for all matches:
=FILTER(C2:C11, E2=A2:A11)
This particular formula looks in the range C2:C11 and returns the corresponding values in the range A2:A11 for all rows where the value in C2:C11 is equal to E2.
The following example shows how to use this syntax in practice.
Example: Use VLOOKUP to Return All Matches
Suppose we have the following dataset in Google Sheets that shows information about various basketball teams:
Suppose we use the following formula with VLOOKUP to look up the team “Rockets” in column A and return the corresponding points value in column C:
=VLOOKUP(E2, A2:C11, 3, FALSE)
The following screenshot shows how to use this formula in practice:
The VLOOKUP function returns the value in the “Points” column for the first occurrence of Rockets in the “Team” column, but it fails to return the points values for the other two rows that also contain Rockets in the “Team” column.
To return the points values for all rows that contain Rockets in the “Team” column, we can use the FILTER function instead.
Here’s the exact formula we can use:
=FILTER(C2:C11, E2=A2:A11)
The following screenshot shows how to use this formula in practice:
Notice that the FILTER function returns all three points values for the three rows where the “Team” column contains Rockets.
The following tutorials explain how to perform other common tasks in Google Sheets:
Cite this article
stats writer (2024). How can Google Sheets be used to return all matches using VLOOKUP?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-google-sheets-be-used-to-return-all-matches-using-vlookup/
stats writer. "How can Google Sheets be used to return all matches using VLOOKUP?." PSYCHOLOGICAL SCALES, 27 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-google-sheets-be-used-to-return-all-matches-using-vlookup/.
stats writer. "How can Google Sheets be used to return all matches using VLOOKUP?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-google-sheets-be-used-to-return-all-matches-using-vlookup/.
stats writer (2024) 'How can Google Sheets be used to return all matches using VLOOKUP?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-google-sheets-be-used-to-return-all-matches-using-vlookup/.
[1] stats writer, "How can Google Sheets be used to return all matches using VLOOKUP?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can Google Sheets be used to return all matches using VLOOKUP?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.