Excel Formulas

You are an Excel formula expert. The user wants to write advanced Excel formulas including VLOOKUP, INDEX-MATCH, XLOOKUP, and dynamic arrays to solve real data lookup and transformation problems.

What to check first

• Verify your Excel version supports the formula type (XLOOKUP requires Excel 365 or newer; dynamic arrays require Excel 365)
• Check that your lookup table has a header row and data is organized in columns
• Confirm whether you need exact match or approximate match for VLOOKUP/XLOOKUP

Steps

1. Use VLOOKUP when your lookup column is the leftmost column in your table range: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
2. Use INDEX-MATCH when your lookup column is to the right of your return column: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
3. Use XLOOKUP (Excel 365) for cleaner syntax with built-in error handling: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
4. Enable dynamic arrays by writing formulas that return multiple values and spill automatically across cells
5. Combine FILTER with dynamic arrays to return all matching rows: =FILTER(return_range, (criteria_range1=criteria1)*(criteria_range2=criteria2))
6. Use UNIQUE to eliminate duplicates from a dynamic array result: =UNIQUE(range, [by_col], [exactly_once])
7. Chain SORT with dynamic arrays for sorted results: =SORT(range, [sort_index], [sort_order], [by_col])
8. Stack multiple dynamic array formulas vertically using VSTACK or horizontally using HSTACK

Code

' VLOOKUP Example - lookup employee ID, return salary (exact match)
=VLOOKUP(A2, EmployeeData!A:D, 4, FALSE)

' INDEX-MATCH Example - find department by name (flexible, right-to-left capable)
=INDEX(DepartmentList!B:B, MATCH(C2, DepartmentList!A:A, 0))

' XLOOKUP Example - cleaner syntax with error handling (Excel 365)
=XLOOKUP(F2, Products!A:A, Products!C:C, "Not Found", 0)

' XLOOKUP with multiple criteria using helper column or array condition
=XLOOKUP(1, (Region!A:A=G2)*(Year!B:B=2024), Sales!C:C, 0)

' Dynamic Array - FILTER to return all sales above threshold
=FILTER(SalesData!A:D, SalesData!D:D>1000)

' Dynamic Array - FILTER with multiple conditions
=FILTER(EmployeeList!A:C, (Employee

Note: this example was truncated in the source. See the GitHub repo for the latest full version.

Common Pitfalls

• Treating this skill as a one-shot solution — most workflows need iteration and verification
• Skipping the verification steps — you don't know it worked until you measure
• Applying this skill without understanding the underlying problem — read the related docs first

When NOT to Use This Skill

• When a simpler manual approach would take less than 10 minutes
• On critical production systems without testing in staging first
• When you don't have permission or authorization to make these changes

How to Verify It Worked

• Run the verification steps documented above
• Compare the output against your expected baseline
• Check logs for any warnings or errors — silent failures are the worst kind

Production Considerations

• Test in staging before deploying to production
• Have a rollback plan — every change should be reversible
• Monitor the affected systems for at least 24 hours after the change