Table of Contents
VLOOKUP is a powerful tool in Google Sheets that allows users to search for specific values in a table and return corresponding data from another column. In order to use VLOOKUP with multiple criteria, users must first set up their data in a way that includes all the criteria they wish to search for. This can be achieved by organizing the data in columns and rows, with the criteria listed in separate columns. Then, by using the VLOOKUP function with the “ARRAYFORMULA” function, users can input multiple criteria to search for and retrieve the desired data. This enables users to efficiently and accurately search for specific information from a large dataset in Google Sheets.
Google Sheets: Use VLOOKUP with Multiple Criteria
Often you may want to use VLOOKUP in Google Sheets to look up values based on multiple criteria.
For example, suppose we have the following two datasets and you would like to use VLOOKUP in the right dataset to return the value in the Points column that matches both the value in the Team column and the Position column in the left dataset:
The following step-by-step example shows how to do so.
Step 1: Create a Helper Column
Before we can perform the VLOOKUP, we must first create a helper column in the first dataset that joins together the values in the Team and Points columns.
We’ll type the following formula into cell A2:
=B2&C2We’ll then drag and fill this formula down to each remaining cell in column A:
Column A now contains a concatenation of the Team and Position values.
We will use this new column in our VLOOKUP formula in the next step.
Step 2: Use VLOOKUP with Multiple Criteria
Next, we can type the following formula into cell I2:
=VLOOKUP(F2&G2, $A$2:$D$13, 4, FALSE)We’ll then drag and fill this formula down to each remaining cell in column I:
Notice that the points value for each player in the right dataset matches the points value for the corresponding player in the left dataset.
We have successfully used a VLOOKUP with multiple criteria.
Cite this article
stats writer (2024). How can I use VLOOKUP with multiple criteria in Google Sheets?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-vlookup-with-multiple-criteria-in-google-sheets/
stats writer. "How can I use VLOOKUP with multiple criteria in Google Sheets?." PSYCHOLOGICAL SCALES, 27 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-vlookup-with-multiple-criteria-in-google-sheets/.
stats writer. "How can I use VLOOKUP with multiple criteria in Google Sheets?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-vlookup-with-multiple-criteria-in-google-sheets/.
stats writer (2024) 'How can I use VLOOKUP with multiple criteria in Google Sheets?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-vlookup-with-multiple-criteria-in-google-sheets/.
[1] stats writer, "How can I use VLOOKUP with multiple criteria in Google Sheets?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I use VLOOKUP with multiple criteria in Google Sheets?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.