Combining Excel arrays with the VSTACK function

This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Combining Excel arrays with the VSTACK function
This blog explains how to use Excel's VSTACK function to combine two lists from separate worksheets into a single array which you can pass into other functions.

Posted by Andrew Gould on 25 July 2025

In this blog

The VSTACK Function
Removing Duplicates
Looking up related values
Filtering the results

On a recent Excel course, a delegate asked how to combine lists of names from two separate worksheets to form a single list to use in a lookup formula.

Here's a breakdown of some mock data using actors, directors and films as substitutes for the real-world information (you can click here to download this file):

A list of actors in a Microsoft Excel worksheet

The Actors worksheet shows a list of actors along with films they starred in.

A list of directors in a Microsoft Excel worksheet

The Directors worksheet shows a list of directors and films.

Each actor appears once only on the Actors worksheet but may also appear once on the Directors sheet. Each director appears once on the Directors sheet but may also appear once on the Actors sheet.

Our task is to create a combined list of actors and directors where each person appears only once. We can use this list to look up any film they either directed or acted in.

The VSTACK Function

You can use the VSTACK function to combine multiple arrays and stack them vertically (there's also an HSTACK function if you want to stack arrays horizontally). The syntax of the function is shown below:

VSTACK(Array1, [Array2],...)

You can combine up to 254 arrays in the VSTACK function - fortunately, we only need to combine 2!

In the Combined List worksheet, I added some column headers and then entered a formula in cell A2 as shown below:

An Excel formula using the VSTACK function to combine two arrays

In the VSTACK function I've referred to the cells containing the list of actor names and director names.

This returns a single array containing all the actor and director names.

A list of actors and directors produced with the VSTACK function in Excel

The combined list contains 3103 names.

Removing Duplicates

We know that some names appear in both the Actors and Directors worksheets, so our list contains some duplicate names. You can see this more easily by wrapping the SORT function around the results of the VSTACK function.

Sorting an array with Excel's SORT function

You can use the SORT function to sort an Excel array.

Scrolling through the list reveals some duplicate names.

A sorted list of actors and directors in an Excel worksheet

Alfred Hitchcock must appear in both lists.

You can remove the duplicates by wrapping the UNIQUE function around the results of the SORT function.

An Excel formula to return a sorted list of unique values from a combined array of two cell ranges

This will remove duplicate values from the array.

The final list contains fewer names.

A list of actors and directors in an Excel worksheet

The final list has only 3032 names.

Looking up related values

Now that we have the list of unique names, we can use lookup functions to return other details. The XLOOKUP function is a good choice for this example (you can learn more about this function in this blog or video).

This checks for anyone whose name appears in the Directors sheet and returns the name of the film.

Here's a breakdown of the arguments provided to the XLOOKUP function shown above:

Argument	What it means
A2#	Look for the value of this cell (the # symbol references every cell in the array which begins in A2).
Directors!A2:A1120	Look in this range of cells on the Directors sheet for a matching name.
Directors!B2:B1120	Return a value from this range of cells on the Directors sheet.
""	If no matching name is found, return an empty string.

You can see the results below:

Results of an XLOOKUP function in Microsoft Excel

You can see the name of the film for anyone whose name appears on the Directors sheet.

You can create a similar formula to look for names which appear on the Actors sheet.

This will look for matching names on the Actors worksheet.

The results of the two functions look something like this:

An appropriate result is returned for each person.

Filtering the results

It would be useful to see a list of only those people who appear in both the Actors and Directors worksheets. You can use the FILTER function to do this (you can learn more about the FILTER function in this blog or this video).