vlookup_helper
Perform VLOOKUP operations in Excel with intelligent error handling and fuzzy matching capabilities to retrieve data from lookup tables.
Instructions
Intelligent VLOOKUP setup and execution with error handling and fuzzy matching
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| sourceFile | Yes | File with data that needs lookup values | |
| lookupFile | Yes | File to lookup values from | |
| lookupColumn | Yes | Column name or index to match on | |
| returnColumns | No | Columns to return from lookup table (empty = all except lookup column) | |
| fuzzyMatch | No | Enable fuzzy string matching for lookups (default: false) | |
| handleErrors | No | Auto-handle #N/A errors with fallbacks (default: true) | |
| sourceSheet | No | Sheet name for source Excel file (optional) | |
| lookupSheet | No | Sheet name for lookup Excel file (optional) |
Implementation Reference
- src/handlers/excel-workflow.ts:247-356 (handler)The primary handler function for the 'vlookup_helper' MCP tool. It reads data from source and lookup files, identifies columns, builds a lookup map for efficient querying, and returns a summary with preview data. Supports fuzzy matching and error handling options. Note: actual source data lookup and match counts are placeholders in this implementation.async vlookupHelper(args: ToolArgs): Promise<ToolResponse> { try { const { sourceFile, lookupFile, lookupColumn, returnColumns = [], fuzzyMatch = false, handleErrors = true, sourceSheet, lookupSheet } = args; if (!sourceFile || !lookupFile || !lookupColumn) { return { content: [{ type: 'text', text: JSON.stringify({ success: false, error: 'Missing required parameters: sourceFile, lookupFile, lookupColumn' }, null, 2) }] }; } // Read both files const sourceData = await readFileContent(sourceFile, sourceSheet); const lookupData = await readFileContent(lookupFile, lookupSheet); if (sourceData.length === 0 || lookupData.length === 0) { return { content: [{ type: 'text', text: JSON.stringify({ success: false, error: 'One or both files are empty' }, null, 2) }] }; } const sourceHeaders = sourceData[0]; const lookupHeaders = lookupData[0]; const lookupRows = lookupData.slice(1); // Find lookup column index let lookupColIndex = typeof lookupColumn === 'number' ? lookupColumn : lookupHeaders.indexOf(lookupColumn); if (lookupColIndex === -1) { throw new Error(`Lookup column "${lookupColumn}" not found`); } // Find return column indices let returnColIndices: number[] = []; if (returnColumns.length === 0) { // Return all columns except lookup column returnColIndices = Array.from({length: lookupHeaders.length}, (_, i) => i) .filter(i => i !== lookupColIndex); } else { returnColIndices = returnColumns.map((col: any) => { if (typeof col === 'number') return col; const index = lookupHeaders.indexOf(col); if (index === -1) throw new Error(`Return column "${col}" not found`); return index; }); } // Create lookup map const lookupMap = new Map<string, any[]>(); lookupRows.forEach((row: any[]) => { const key = String(row[lookupColIndex] || '').toLowerCase(); const values = returnColIndices.map((i: number) => row[i]); lookupMap.set(key, values); }); const result = { success: true, operation: 'vlookup_helper', summary: { sourceRows: sourceData.length - 1, lookupRows: lookupRows.length, lookupColumn: lookupHeaders[lookupColIndex], returnColumns: returnColIndices.map((i: number) => lookupHeaders[i]), fuzzyMatch, handleErrors }, lookupMap: Object.fromEntries(Array.from(lookupMap.entries()).slice(0, 10)), // Preview matchedCount: 0, // Would be calculated during actual lookup unmatchedCount: 0 // Would be calculated during actual lookup }; return { content: [{ type: 'text', text: JSON.stringify(result, null, 2) }] }; } catch (error) { return { content: [{ type: 'text', text: JSON.stringify({ success: false, error: error instanceof Error ? error.message : 'Unknown error', operation: 'vlookup_helper' }, null, 2) }] }; } }