Lookup & Reference
VSTACK Function in Excel
Appends arrays vertically (stacks rows).
Syntax
- =VSTACK(array1, [array2], ...)
Arguments
- array1 (required): First array
- array2 (optional): Additional arrays to stack
Examples
- =VSTACK(A1:B2, A5:B6) - Stack two 2x2 arrays - Result: 4x2 array
When to use VSTACK
- Use VSTACK when you need to retrieve values from a table or range based on a key.
- Confirm data types match (text vs number) and trim spaces before lookups.
- Pair with [IFERROR](/functions/iferror/) or [IFNA](/functions/ifna/) for cleaner reports.
- See the [lookup functions guide](/guides/excel-lookup-functions-guide/) for VLOOKUP, XLOOKUP, and INDEX MATCH paths.
Common VSTACK mistakes
- #N/A often means the key is missing or exact match is wrong — see [#N/A error](/errors/na/).
- Lock table references with $ when copying formulas down a column.
- Compare legacy vs modern lookups in [XLOOKUP vs VLOOKUP](/compare/xlookup-vs-vlookup/).
Common errors
- Requires Excel 365
- Arrays should have same column count
Use cases
- Combine tables
- Append data
- Union arrays
Frequently asked questions
- How do I combine data from multiple sheets? Use =VSTACK(Sheet1!A1:D10, Sheet2!A1:D10, Sheet3!A1:D10) to stack data from three sheets vertically. All ranges should have the same number of columns. Great for consolidating monthly reports.
- How do I add a header row to filtered results? Use =VSTACK(A1:D1, FILTER(A2:D100, criteria)) to combine the header row with filtered data. The header stays fixed while the filtered rows change dynamically.
- What happens if arrays have different column counts? VSTACK pads shorter rows with #N/A to match the widest array. Use IFERROR to replace #N/A with blanks: =IFERROR(VSTACK(array1, array2), ""). Or ensure all arrays have the same column count.
Editorial review
- Reviewed by Excel.Directory Editorial Team. Updated May 2026.