The Problem Was 「Repetition」
When running a B2B business, one of the most time-consuming tasks is data matching. Linking order IDs with customer information, matching product codes to inventory, connecting shipping documents to delivery statuses, these tasks repeat every single day. Over the past three months, this type of work alone has consumed approximately 5 hours per week.
「The same data, yet I had to keep searching across different spreadsheets」
Meeting Claude Code
Last Monday, I started an experiment in Google Sheets on a Windows device. The goal was to use Claude Code's automation capabilities not just for simple sorting or filtering, but to implement 「dynamic search」.
First Attempt (Failed):
= VLOOKUP(A2, B:D, 3, FALSE)
Basic VLOOKUP wasn't enough. The search range had to change each time.
The 72-Hour Turning Point
Tuesday afternoon, I dove deep into Claude Code's code analysis features.
Improved Approach:
= IFERROR(
INDEX(
FILTER(Sheet2!C:C, Sheet2!A:A = A2),
1
),
「Not found」
)
When combining the FILTER function with INDEX, something dramatic happened. I could now search multiple sheets simultaneously using the same condition. By Wednesday morning, the spreadsheet with this formula started automatically pulling related information as data was entered.
Results:
• Weekly 5-hour manual work reduced to 10 minutes
• Data error rate 0% (manual work typically had 3~4% errors)
• Can adapt to new data sources with only minor modifications
Two Pitfalls a Non-Developer Encountered
1. Errors When Ranges Change Dynamically
Partner company A kept adding new data, causing the range to expand. Initially, I had to modify the formula every time new rows were added. The solution was to specify the entire column (A:A) as the range.
2. When Multiple Matching Conditions Are Needed
Situations arose where searching wasn't just by ID alone, but by a combination of (ID + Date). In this case, I created a temporary search key using CONCATENATE and then used FILTER on it.
Next Steps
I'm working with the dream team bots to apply this pattern to other automation challenges. Now that dynamic reference methods are possible, it's time to move toward the 「prediction phase」.
Key Takeaways:
• Repetitive data matching work can be automated with dynamic formulas
• FILTER + INDEX combination overcomes VLOOKUP limitations
• Claude Code's code analysis helps generate ideas
• Small failures and adjustments accumulate into major efficiency gains