Function Problems

Fix Excel DGET Function Not Working with Inline Criteria and VSTACK Arrays

Maira Mehtab Sep 22, 2026 869 views

Question details

The user is trying to pass inline arrays or VSTACK-generated arrays as the criteria argument in the DGET function, but the formula fails to calculate.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Using database functions like DGET to extract records where the criteria parameter is supplied dynamically via inline array constants or the VSTACK function.
Observed behavior: The DGET function returns an error because it refuses to accept in-memory arrays and strictly requires a worksheet range reference for its criteria.

Before you start

Ensure you have empty cells available in your worksheet, as you will need physical space to output the array criteria so the database function can reference them as a range.

Solution 1Recommended

Use a Worksheet Spill Range for DGET Criteria

Database functions like DGET require a physical worksheet range. You can bypass the array limitation by spilling your array into cells and referencing that spilled range.

The DGET function, along with other Excel database functions (like DSUM and DCOUNT), is a legacy function that was designed to work exclusively with physical cell addresses. As a result, it cannot process virtual arrays held in memory, such as inline array constants (e.g., ={"Header";"Criterion"}) or dynamic arrays generated by functions like VSTACK.

Spill the array criteria into a cell

Select an empty cell in your worksheet and enter your inline array or VSTACK formula to output the criteria physically onto the sheet.

Reference the spill range

In your main DGET formula, click the cell where your criteria array begins and add the '#' operator (for example, J1#) to dynamically reference the entire spilled range.

Execute the formula

Press Enter to calculate the DGET formula. It will now successfully read the criteria from the physical worksheet range.

Modern Function Alternatives: If you prefer not to use physical ranges for criteria, consider using modern array-supported functions like XLOOKUP or FILTER, which natively accept inline arrays and VSTACK formulas.

Free Microsoft Office alternative

Handle Complex Array Formulas with WPS Office

While legacy database functions have inherent limitations in Microsoft Excel, WPS Office provides a powerful, free alternative that supports advanced array formulas, modern lookup functions, and full compatibility with complex datasets.

1. Download WPS Office: Visit the official WPS Office website and download the free installation package for your device.
2. Open Your Spreadsheet: Launch WPS Spreadsheets and open your existing Excel workbook containing the DGET formulas.
3. Edit Without Limitations: Continue modifying your data and formulas with a seamless, highly compatible spreadsheet experience.

Fully compatible with Microsoft Excel formats (.xlsx, .xls) and legacy database functions.Natively supports modern dynamic arrays and advanced lookup functions like FILTER.Lightweight software architecture ensures fast calculation for heavy spreadsheets.Familiar user interface requiring zero learning curve for Excel users.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use an inline array directly in the DGET function?

No. The DGET function, along with other database functions in Excel, strictly requires a worksheet range for its criteria argument. Supplying inline arrays directly will cause an error.

Why does VSTACK work in other formulas but not in DGET criteria?

VSTACK generates a virtual array in memory. Because DGET is a legacy database function, it was designed specifically to read from physical cell addresses on the worksheet rather than processing in-memory arrays.

Are there alternative functions to DGET that support arrays?

Yes. Modern functions such as XLOOKUP, FILTER, and INDEX/MATCH natively support in-memory arrays and VSTACK results without requiring a physical criteria range on the worksheet.

Excel & Data Problems