Every spreadsheet team has a lookup story: a broken VLOOKUP because someone inserted a column, an INDEX/MATCH formula that took three attempts to get right, or a junior analyst asking which function to use on a vendor pricing table. Lookup functions are foundational — and they are also a prime example of where AI agents can remove mechanical friction without eliminating the need to understand your data.
VLOOKUP in plain terms
VLOOKUP searches for a value in the leftmost column of a table and returns a value from a specified column to the right. It is easy to write for simple cases: =VLOOKUP(A2, Products!A:D, 3, FALSE) might return the price for a product SKU. The catch is rigidity — the lookup column must be leftmost, column index numbers break when columns are inserted, and approximate match (TRUE) surprises people who forget the sort requirement.
Why power users prefer INDEX/MATCH
INDEX/MATCH splits the problem: MATCH finds the position of a lookup value in a row or column, and INDEX returns the value at that position. The combination is more flexible — lookup columns can sit anywhere, and inserting columns does not break hard-coded index numbers. For large tables and two-way lookups, INDEX/MATCH (or XLOOKUP on newer Excel versions) is often more maintainable.
How agents change the lookup workflow
With a natural-language agent, you describe the business intent: "Add a Unit Price column to the Orders sheet by looking up each SKU in the Products table and returning Price." The agent chooses an appropriate formula pattern, writes it down the column, and shows a diff. You verify a few rows against known values rather than debugging #N/A errors in the formula bar.
- You still need to know which table is authoritative and which column is the join key.
- Agents handle syntax and range references; humans validate business logic and edge cases.
- When lookups fail, clearer prompts help: specify sheet names, exact header text, and whether duplicates exist.
- For audit trails, ask the agent to explain the formula it inserted — good practice for regulated workflows.
When to learn the functions anyway
Agents accelerate delivery, but spreadsheet literacy still matters for reviewing work, fixing one-off errors offline, and working in environments without AI. Understanding VLOOKUP's limitations makes you a better reviewer of agent output. Understanding INDEX/MATCH makes you faster when you need to tweak a single cell without re-prompting.
The practical takeaway: treat lookups as a collaboration between you and the agent. You own the data model and join keys; the agent owns repetitive formula authoring. That division of labor is where modern Excel workflows save the most time.