How to Perform a VLOOKUP Search on Multiple Columns in Google Sheets

Google Sheets’ VLOOKUP function is a powerful tool that allows you to search for a value within a column of data. For instance, if you have a list of student names and their grades, you can use VLOOKUP to find the grade of a specific student in a particular subject. VLOOKUP is an incredibly useful and popular feature of Google Sheets, used by millions of people every day.

By default, the VLOOKUP formula can only search for a value in a single column. But what if you want to search across multiple columns and then retrieve the corresponding value? For example, let’s say you have a full name (let’s say Mark Wilson) as the search value, but in your dataset, you have the first name and last name in two separate columns. In this case, you need to somehow use VLOOKUP to combine multiple columns and use them as search criteria.

Performing a VLOOKUP Search on Multiple Columns in Google Sheets (Using a Helper Column)

Let’s assume you have the dataset as shown below and you want to retrieve the value corresponding to a name (let’s say Winthrop Pletts) in column C.

The problem here is that we have the first name and last name in separate columns. The trick to make it work is to modify the search array (the range) in a way that multiple columns are combined to give you that single column that contains the combined search value.

So, let’s create a helper column that will have the combined names, and then we can use that helper column to search for the search value. The following steps explain how to create a helper column and perform a VLOOKUP search on the new data.

1. Insert a new column to the left of the column you want to return. In the example, we are creating a new column to the left of “Mailing List Subscriber” as that is the value we want to return. The example shows right-clicking on column C and choosing “Insert 1 left”.

2. Give the new column a suitable header if needed (in the example, we call 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).

3. Apply the formula to the rest of the column. You can do this by selecting the cell with the formula and dragging the square icon at the bottom-right to the bottom of the range.

4. Select the cell you want to use to perform the VLOOKUP search, then enter the formula “=VLOOKUP({Combined Search Criteria},{Data Range},{Index},false)” in the cell. In the example, we are checking if “Britta Dowey” is subscribed. We select the range C2:D16 and set the index to “2” to return the correct value.

5. Now, the cell will return the search criteria. In the example, we used the formula =VLOOKUP(“Britta Dowey”,C2:D16,2,false). Since Britta Dowey is not subscribed, it returns the value “False”.

Note that VLOOKUP can only look to the right, so you need to create the helper column to the left of the column value you want to return.

Performing a VLOOKUP Search on Multiple Columns Using an Array Formula

Using the ARRAYFORMULA function with VLOOKUP is a handy way to create a new set of data with the search key as a single column and display the associated VLOOKUP return values. This method is useful when you need to return multiple values when performing a multiple-criteria search query.

The example data for this method uses a smaller list of customers but includes an additional column for the email list subscription status.

It also features the “Full Name,” “Mailing List Subscriber,” and “Email List Subscriber” columns that we will use for the VLOOKUP search.

1. Create the combined column array table for the search key using the formula “=ARRAYFORMULA({fixed start cell} & ” ” & {fixed end cell})”. In our example, we will use: =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).

2. Create your VLOOKUP formula:

   • The search key should be the first cell in the new combined column with a fixed column parameter. The example uses “$B9”.
   • The range should be a combination of the ArrayFormula data separated by a comma, ending with fixed values for the remaining comparison data. In the example, we are returning values in columns C and D, so it corresponds 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 are returning the value from the third column, so it is “3”.
   • Leave the “is_sorted” value as “false”.
   • Altogether, our example formula is: =VLOOKUP($B9,{$A$2:$A$6 & ” ” & $B$2:$B$6,$C$2:$D$6},3,false)

3. Wrap your VLOOKUP formula in ARRAYFORMULA as follows: =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))

4. Insert the formula into the top-leftmost return cell. This will return the first result.

5. Drag the bottom-right corner icon across the entire return range to fill the rest of the spreadsheet.

Note: If you see reference errors, make sure that ARRAYFORMULA properly envelops VLOOKUP.

The methods detailed in this tutorial show you how to bypass the limitations of VLOOKUP in Google Sheets. With a little effort and modification of your spreadsheets, you can perform a VLOOKUP search on multiple columns.

In this tutorial, we only showed you examples where we had to combine two columns, but you can also use the same method for VLOOKUP on more than two columns.

I hope you found this tutorial helpful!

For more Google Sheets tutorials, visit Crawlan.com!