Custom Functions and Spreadsheet Solver: Your Complete Guide
Key Takeaways
- Flookup custom functions let you perform fuzzy matching, deduplication and text standardisation directly in spreadsheet cells using familiar formulas.
- The Spreadsheet solver is an AI assistant that helps you build the right formulas for your specific data challenges.
- Custom functions are repeatable, auditable and update automatically when your source data changes.
- All Flookup features are available on a single plan with unlimited usage.
Why Custom Functions Change Everything
Data cleaning in spreadsheets used to mean switching between tools, copying data back and forth, or manually editing cells. Custom functions eliminate all that friction by bringing powerful data cleaning capabilities directly into your spreadsheet cells.
Instead of navigating menus or sidebars, you simply type a formula like =FLOOKUP(A2, B2:B100, 1, 2, 0.85) and get instant fuzzy matching results. The formula updates automatically when your data changes, making your cleaning workflow repeatable and auditable.
Advantages of Formula-Based Cleaning
- Repeatability: Once you write a formula, you can drag it down to process thousands of rows. Need to clean a new dataset? Copy the formula.
- Transparency: Every transformation is visible in the formula bar. You can see exactly how each result was calculated.
- Dynamic updates: Change your source data and the results update automatically. No need to re-run cleaning operations.
- No context switching: Stay in your spreadsheet. No need to export data, open a separate tool, then import results back.
The Core Custom Functions
Flookup provides a suite of custom functions designed for the most common data cleaning tasks. Here are the essentials:
FLOOKUP: Fuzzy Matching
The =FLOOKUP() function finds the best match for a value in a lookup table, even when there are spelling variations or typos.
=FLOOKUP(lookup_value, table_array, lookup_col, index_num, threshold, output_mode)Example: You have a list of company names in column A and a master list in columns D-E. To find the industry for "Acme Corp" (even if the master list has "Acme Corporation"):
=FLOOKUP(A2, D2:E100, 1, 2, 0.85, "score")This returns the industry from column E along with a similarity score showing how confident the match is.
NORMALIZE: Text Standardisation
The =NORMALIZE() function cleans text by removing diacritics, punctuation, stop words, or extracting domains from URLs.
=NORMALIZE(lookup_value, stop_array, threshold, operation)Example: Clean a column of addresses by removing punctuation and diacritics:
=NORMALIZE(A2:A100, , , "punctuations")DEDUPE: Remove Duplicates
The =DEDUPE() function removes duplicate rows based on fuzzy similarity, not just exact matches.
=DEDUPE(data_range, key_column, keep_mode, match_type, threshold)Example: Remove duplicate customer names from a list, keeping the first occurrence of each:
=DEDUPE(A2:C100, 2, "first", "percentage", 0.85)FUZZYSIM: Compare Text Similarity
The =FUZZYSIM() function calculates how similar two text strings are, returning a score from 0 to 1.
=FUZZYSIM(left_string, right_string, compare_mode)Example: Compare two product descriptions:
=FUZZYSIM(A2, B2, "by_word")ULIST: Extract Unique Values
The =ULIST() function extracts unique values from a list, treating similar entries as duplicates.
=ULIST(col_array, index_num, threshold, operation)Example: Get a unique list of city names, treating "New York" and "NYC" as the same:
=ULIST(A2:A100, 1, 0.85, "by_percentage")Meet the Spreadsheet Solver
The Spreadsheet solver is your AI-powered assistant for building the right formulas. Instead of memorizing function syntax or figuring out which parameters to use, you simply describe what you want to achieve.
How It Works
- Describe your goal: Tell the solver what you want to accomplish in plain language. For example: "I need to match customer names from my sales data against a master customer list, even when there are spelling differences."
- Get a formula suggestion: The solver analyzes your request and suggests the appropriate Flookup function with the right parameters.
- Refine if needed: If the suggestion is not quite right, ask follow-up questions or provide more context.
When to Use the Solver
- You are not sure which Flookup function to use for your task
- You need help figuring out the right parameters (threshold, column indexes, etc.)
- You want to combine multiple functions for a complex workflow
- You are new to Flookup and want guidance on best practices
Access the Spreadsheet solver via Extensions > Flookup Data Wrangler > Spreadsheet solver in your Google Sheets menu.
Practical Examples
Example 1: Reconciling Vendor Lists
Scenario: You have a list of vendor names from invoices (column A) and a master vendor database (columns D-E with name and vendor ID). You need to match each invoice vendor to the correct ID.
Solution:
=FLOOKUP(A2, D2:E500, 1, 2, 0.85, "score")This finds the best match for each invoice vendor in the master list and returns the vendor ID with a confidence score. Drag the formula down to process all rows.
Example 2: Cleaning Contact Data
Scenario: You imported contact data from multiple sources and need to standardize phone numbers and remove duplicates.
Solution:
- First, normalize the data:
=NORMALIZE(A2:A1000, , , "punctuations") - Then remove duplicates:
=DEDUPE(B2:B1000, 1, "first", "percentage", 0.9)
Example 3: Finding Similar Products
Scenario: You have two product catalogs from different suppliers and need to identify which products are the same despite different naming conventions.
Solution:
=FUZZYSIM(A2, B2, "by_phrase")This compares product descriptions and returns a similarity score. Products with scores above 0.8 are likely the same item.
Workflow Tips
Start with the Demo
Before installing Flookup, try all the custom functions in our live demo spreadsheet. No installation or account required.
Use the Right Threshold
The threshold parameter (default 0.75) controls how strict the matching is. Lower values (0.65-0.75) find more matches but may include false positives. Higher values (0.85-0.95) are more precise but may miss valid matches. Start with the default and adjust based on your results.
Normalize Before Matching
For best results, clean your text data before performing fuzzy matching. Remove punctuation, diacritics, and extra spaces using =NORMALIZE(). This improves matching accuracy significantly.
Process in Batches
Custom functions have a 30-second execution timeout. For very large datasets (10,000+ rows), process data in batches or use the Schedule functions feature for automated processing.
Getting Started
Installation
- Install the Flookup Data Wrangler add-on from the Google Workspace Marketplace.
- Open your Google Sheet and go to Extensions > Flookup Data Wrangler > Account management > Profile activation.
- Enter your Profile ID to activate your plan.
- Start using custom functions in your cells!
Try Before You Buy
Explore all custom functions in our live demo spreadsheet. No installation, no credit card needed.
Frequently Asked Questions
What are Flookup custom functions?
Flookup custom functions are spreadsheet formulas you type directly into Google Sheets cells, like =FLOOKUP() or =NORMALIZE(). They perform fuzzy matching, deduplication, text standardisation and other data cleaning tasks without requiring any menu navigation.
How does the Spreadsheet solver work?
The Spreadsheet solver is an AI assistant that helps you build formulas and solve data problems. You describe what you want to achieve in plain language, and it suggests the appropriate Flookup function or Google Sheets formula to accomplish your goal.
Do I need to install anything to use custom functions?
Yes, you need to install the Flookup Data Wrangler add-on from the Google Workspace Marketplace. Once installed, all custom functions become available in your spreadsheet. You can try them first in our live demo without installation.