Function Problems

How to Match Verb Variations Using a Keyword List in Excel

Tauseeq Magsi Oct 1, 2026 868 views

Question details

The user wants to detect specific words in an Excel cell by comparing them against an external list of verbs, specifically needing a way to match related verb forms (e.g., change and changed).

How to Match Verb Variations Using a Keyword List in Excel

Product: Excel
Device & OS: not provided
Scenario: Comparing text data in one workbook against a keyword list stored in another workbook to identify linguistic matches and verb variations.
Observed behavior: Standard Excel formulas do not automatically identify linguistic variations, and accessing the external keyword list is being blocked by shared workbook permission issues.

Before you start

Ensure that the external workbook containing your keyword list is publicly accessible or that you have the necessary file permissions to link it without access requests.

Solution 1

Resolve Permission Issues for External Workbooks

If your keyword list is in a shared external workbook, you must ensure it does not require permission requests to function correctly with formulas.

Excel formulas and VBA scripts will fail to retrieve data if the linked workbook prompts for user access permissions.

Open the external workbook

Open the shared workbook that contains your verb keyword list in your web browser or desktop app.

Update sharing settings

Navigate to the 'Share' button in the top right corner and update the link settings to 'Anyone with the link can view' or ensure your specific account is granted direct access.

Refresh links in your main workbook

Go back to your main workbook, navigate to the 'Data' tab, click 'Edit Links', and select 'Update Values' to ensure the connection is restored.

Solution 2Recommended

Use Wildcard Searches with Array Formulas

Excel formulas don't understand grammar, so you must use wildcards to match root words against variations.

Solution 3

Use Power Query for Advanced Text Matching

Power Query allows for more robust text normalization and cross-workbook comparisons.

Efficient Data Processing with WPS Office

Easily Match and Process Text Data in WPS Spreadsheets

WPS Spreadsheets provides powerful array formulas, wildcard support, and robust external link features to help you match keyword lists across workbooks seamlessly.

1. Open your files: Launch WPS Spreadsheets and open both your main dataset and the keyword list workbook.
2. Format root keywords: Ensure your keyword list contains root forms of verbs to allow for partial matching.
3. Apply matching formula: Use the SEARCH and SUMPRODUCT functions to cross-reference the text in your main file with the external keyword array.
4. Drag to fill: Use the fill handle to apply the matching logic across your entire dataset quickly.

Fully compatible with Microsoft Excel formulas and .xlsx files.Advanced wildcard support for searching text variations.Lightweight application with rapid calculation for large keyword arrays.Built-in data tools for cross-workbook referencing without lagging.

Frequently Asked Questions

Why doesn't EXACT or VLOOKUP find my verb variations?

Functions like EXACT and VLOOKUP look for precise matches. They view 'change' and 'changed' as completely different text strings. You must use partial matching functions like SEARCH or maintain a comprehensive list of all variations.

Can I use fuzzy matching in standard formulas?

Standard formulas do not support fuzzy matching natively. You will need to use Power Query's fuzzy merge feature or write custom VBA scripts to evaluate linguistic similarity.

How do I fix a #REF! error when linking an external keyword list?

A #REF! error often occurs if the external workbook is moved, renamed, or restricted by permission settings. Check the Data tab, open Edit Links, verify the file path, and ensure the workbook is shared with proper access rights.

Excel & Data Problems