With the help of the ARRAYFORMULA function, we can retrieve multiple values using the VLOOKUP function in Google Sheets.

There are two distinct scenarios when VLOOKUP returns multiple values in combination with ARRAYFORMULA:

1. Using Multiple Search Keys: You can look up two or more values simultaneously within a column.
2. Using Multiple Column Indexes: You can retrieve values from specific cells or the entire row of the matching entry.

Using Multiple Search Keys and Retrieving Multiple Result Values with VLOOKUP

In the following example, I have sample data in one of my Google Sheets in the range B3:C8. Here, B3:B8 contains player names, and C3:C8 contains their ages.

In this sample data, we can use the following VLOOKUP formula to return the age of a single person whose name is specified in cell E3:

=VLOOKUP(E3, B3:C8, 2, 0)

This formula follows the syntax:

VLOOKUP(search_key, range, index, [is_sorted])

Where:

• search_key: E3
• range: B3:C8
• index: 2 (result column in the range)
• is_sorted: 0. It represents an exact match of the search_key (you can specify FALSE as well)

To retrieve two values, the ages of multiple persons in our context, specify their names in cells E3:E4 and use the following formula:

=ARRAYFORMULA(VLOOKUP(E3:E4, B3:C8, 2, 0))

Note: If you hardcode the criteria instead of specifying them as E3:E4, use the VSTACK function as shown below:

VSTACK(search_key1, search_key2, ...)

This example demonstrates how to retrieve multiple values using multiple search keys in VLOOKUP.

Using VLOOKUP to Retrieve All Values from the Matching Row (Using Multiple Column Indexes)

Sample Data:

In the following example, I have product names in column A and sales from the first half of the year 2017 in columns B to G.

The actual range for lookup is A3:G6. Please refer to the screenshot provided below.

Lookup:

Enter the product name to search in cell A8.

To search it and return sales values from Jan to Jun, you can use the following VLOOKUP formula in cell B8.

=ARRAYFORMULA(VLOOKUP(A8, A3:G6, {2, 3, 4, 5, 6, 7}, 0))

This example demonstrates how to retrieve multiple values using multiple index numbers in VLOOKUP.

Formula Explanation

In this example, the search key is from cell A8, which is used to search for the product name in the first column within the range A3:G6.

The match is found in cell A4, so the values from cells B4 to G4 in the same row are returned.

This demonstrates the use of the VLOOKUP formula to return all or selected values from a row based on a match in the first column of that row.

How Do These Multiple Values (Multiple-Column Indices) in VLOOKUP Work?

When you want to return a single value using VLOOKUP, you can use a formula similar to the one below:

=VLOOKUP(A4, A3:G6, 2, FALSE)

The above VLOOKUP formula will find the search key in cell A4 and return the value from the second column in the same row.

When you want to return multiple values using VLOOKUP, you should use multiple column indexes within curly braces, as shown below:

{2, 3, 4}

This creates an array that can return multiple column values in Google Sheets, specifically values from columns 2, 3, and 4. However, do not use the formula as follows:

=VLOOKUP(A4, A3:G6, {2, 3, 4}, FALSE)

We have utilized curly braces to generate an array within the VLOOKUP formula. However, VLOOKUP is not an array formula by default.

Therefore, we should additionally use an ARRAYFORMULA function with the above formula. The final formula will be as follows:

=ARRAYFORMULA(VLOOKUP(A4, A3:G6, {2, 3, 4}, FALSE))

This way, you can return multiple values using VLOOKUP in Google Sheets.

VLOOKUP Array Formula with Multiple Search Keys and Column Indexes

We have seen the use of VLOOKUP with ARRAYFORMULA in Google Sheets.

In the first set of examples, we used more than one search key from the same column to return an array output. In the second example, we specified multiple column indexes to return an array output.

Can we combine them both to get a 2D array result? The answer is yes.

In the last example, if you want to look up ‘Product 2’ and ‘Product 4’ entered in cells A8 and A9, you can use the formula below. It will return the values from the monthly columns.

=ARRAYFORMULA(VLOOKUP(A8:A9, A3:G6, {2, 3, 4, 5, 6, 7}, 0))

As a side note, this type of 2D array result is not supported in XLOOKUP, another new lookup function, in the regular way.

But keep in mind that XLOOKUP is a newer and potentially better alternative for some VLOOKUP use cases. This is because XLOOKUP offers more flexibility due to its ability to use separate lookup and result ranges.

Unlike VLOOKUP, which is restricted to searching only the first column of a range (though this can be overcome with workarounds), XLOOKUP allows you to search in any column.

This can significantly simplify formulas, especially when working with complex data structures.

Resources

• How to Use VLOOKUP with Multiple Criteria in Google Sheets
• Dynamic Index Column in Vlookup in Google Sheets
• Vlookup Result Plus Next ‘n’ Rows in Google Sheets