Brainforge Knowledge

wholesale_arrayformula_fix

3 min read

Fix: “A matching value could not be found” Error

The Error

You’re seeing: “A matching value could not be found. To get the values for the entire range use the ARRAYFORMULA function.”

This happens when you try to reference a range like 'Wholesale Customers Data - From Snowflake'!A2:AP without proper array syntax.

The Fix

You need to use ARRAYFORMULA or array literal syntax to pull an entire range.

In A2, replace your formula with:

=ARRAYFORMULA('Wholesale Customers Data - From Snowflake'!A2:AP)

This explicitly tells Google Sheets to treat this as an array formula that spills across the entire range.

Solution 2: Use Array Literal Syntax

In A2:

={'Wholesale Customers Data - From Snowflake'!A2:AP}

The curly braces {} create an array literal.

Solution 3: Use ARRAYFORMULA with IF (More Robust)

In A2:

=ARRAYFORMULA(IF('Wholesale Customers Data - From Snowflake'!A2:A="", "", 'Wholesale Customers Data - From Snowflake'!A2:AP))

This handles empty rows gracefully.

Why This Error Happens

When you write:

='Wholesale Customers Data - From Snowflake'!A2:AP

Google Sheets tries to find a single matching value in that range, which fails. You need to tell it to pull the entire range as an array.

Complete Working Formula

For “Report - All Wholesale Customers” sheet:

  1. Row 1: Copy headers manually (static text)

  2. Row 2, Cell A2:

=ARRAYFORMULA('Wholesale Customers Data - From Snowflake'!A2:AP)
  1. This will:
    • Pull all data from row 2 onwards
    • Spill across all columns (A through AP)
    • Update automatically when source changes
    • Work with Filter Views (not regular filters)

If You Want Default Sorting

In A2:

=ARRAYFORMULA(SORT('Wholesale Customers Data - From Snowflake'!A2:AP, 16, FALSE))

Where:

  • 16 = Column P (TOTAL_REVENUE)
  • FALSE = Descending order

Important: Filters Still Need Filter Views

Even with ARRAYFORMULA, regular filters will still break. You must use:

Filter Views instead:

  1. Data → Filter views → Create new filter view
  2. Apply your filters and sorting
  3. Save the view

OR use the two-sheet approach:

  1. Background sheet: =ARRAYFORMULA('Wholesale Customers Data - From Snowflake'!A2:AP)
  2. Report sheet: ='Background Sheet'!A2:AP (direct reference, not array)
  3. Regular filters work on the report sheet

Quick Fix Steps

  1. Clear the error:

    • Select cell A2
    • Delete the current formula

  2. Enter the correct formula:

    =ARRAYFORMULA('Wholesale Customers Data - From Snowflake'!A2:AP)

  3. Press Enter

    • Data should populate

  4. For filtering:

    • Use Filter Views (Data → Filter views)
    • OR set up two-sheet approach

Testing

After applying the fix:

  • Data loads (no VALUE! errors)
  • All columns populated
  • Data updates when source refreshes
  • Can create Filter Views
  • No blank data when filtering

Summary

The Problem: Direct range reference without array syntax The Fix: Use ARRAYFORMULA() or ={} syntax For Filters: Use Filter Views or two-sheet approach

Graph View