VLOOKUP formula (or Vertical lookup) in Google Sheets is a formula that you can use to look for a value in a columnar data set.
For example, if you have a list of student names and their scores, you can use VLOOKUP to find the score of a particular student in a specific subject.
VLOOKUP is an extremely useful and popular Google Sheets function that is used by millions of people on a daily basis.
By default, the VLOOKUP formula can only look for a value in one column. But what if you want to look up multiple columns and then fetch the corresponding value.
For example, you have a full name (say Mark Wilson) as the lookup value but in your data set, you have the first and the last name in two separate columns.
In such a case, you need to somehow use VLOOKUP to combine multiple columns and use these as the lookup criteria.
In this tutorial, I’m going to show you a couple of methods to you use VLOOKUP to use multiple columns as the criteria and fetch the corresponding value.
This Article Covers:
VLOOKUP Multiple Columns in Google Sheets (Using Helper Column)
Suppose you have the dataset as shown below and you want to fetch the value for a name (say Winthrop Pletts) from column C.
The problem here is that we have the first and last names in separate columns.
The trick to making it work is to alter the lookup array (the range) in such a way that multiple columns are combined to give you that one column that has the combined lookup value.
So we would create a helper column that would have the combined names, and then we can use this helper column to search and look for the lookup value
The following steps explain how to create a helper column and run a VLOOKUP on the new data.
- Insert a new column to the left of the column you’re looking to return. In the example, we’re creating a new column to the left of “Mailing List Subscriber” since that’s the value we want to return. The example shows right-clicking on column C and choosing “Insert 1 left.”
- Give the new column an appropriate header if applicable (in the example we’re naming it “Full Name”). Then enter the formula “=({first cell}&” “&{second cell})” in the topmost cell of the helper column. The example uses the formula =(A2&” “&B2).
- Apply the formula to the rest of the column. You can do this by selecting the cell with the formula and dragging the bottom right square icon to the bottom of the range.
- Select the cell you want to use to perform the VLOOKUP, then enter the formula “=vlookup({Combined Search Criteria},{Data Range},{Index},false)” in the cell. In the example case, we’re checking to see if “Britta Dowey” is subscribed. We’re selecting the range C2:D16, and setting the index to “2” to return the correct value.
- Now the cell will return the search criteria. In the example, we used the formula =vlookup(“Britta Dowey”,C2:D16,2,false) . Britta Dowey is not a subscriber, so it returns the value “False.”
Remember, VLOOKUP can only look to the right, so you’ll need to create the helper column to the left of the column value you want to return.
VLOOKUP Multiple Columns Using Array Formula
Using the ARRAYFORMULA function with VLOOKUP is a helpful way to create a new data set with the search key as a single column and show the related VLOOKUP return values.
This method is helpful when you need to return multiple values when running a multiple value search query.
The example data for this method uses a smaller list of customers but has an added column for email list subscription status.
It also features “Full Name,” “Mailing List Subscriber,” and “Email List Subscriber” columns we’ll be using for the VLOOKUP.
- Create the array of combined columns for the search key using the “=ArrayFormula({static start cell}&” “&{static end cell})” formula. In our example, we’ll be using: =ArrayFormula($A$2:$A$6&” “&$B$2:$B$6). Enter this formula in the topmost cell under the new combined column (B9 in the example).
- Create your VLOOKUP formula.
- The search key should be the first cell in the new combined column with a static column setting. The example uses “$B9.”
- The range should be a combination of the ArrayFormula data separated by a comma that ends with static values for the rest of the comparison data. In the example, we’re returning values in columns C and D so this comes to {$A$2:$A$6&” “&$B$2:$B$6,$C$2:$D$6}.
- Set the index for the return column number. In the example, we’re returning the third column value so it is “3.”
- Leave the “is_sorted” value as “false.”
- All together, our example formula is: =vlookup($B9,{$A$2:$A$6&” “&$B$2:$B$6,$C$2:$D$6},3,false)
- Wrap your VLOOKUP formula in ARRAYFORMULA like =ArrayFormula({your vlookup formula}). The example uses the formula: =ArrayFormula(vlookup($B9,{$A$2:$A$6&” “&$B$2:$B$6,$C$2:$D$6},3,false))
- Insert the formula into the topmost, leftmost return cell. This will return the first result.
- Drag the bottom right corner square icon across the entire return range to populate the rest of the spreadsheet.
Note: If you’re seeing reference errors, make sure the ARRAYFORMULA is correctly wrapping the VLOOKUP.
The methods detailed in this tutorial demonstrate how you can work around VLOOKUP limitations in Google Sheets. With a little effort and spreadsheet modification, you can run VLOOKUP multiple columns.
In this tutorial, we have only shown you examples where we had to combine two columns, but you can also use the same method to Vlookup more than two columns as well.
I hope you found this tutorial useful!
Other Google Sheets tutorials you may like:
