Excel Advanced Formulas: VLOOKUP, INDEX-MATCH, XLOOKUP & More

Why Excel Skills Matter in India

Excel proficiency separates junior and mid-level professionals in India across finance, HR, operations, and sales. VLOOKUP and Pivot Tables are asked in 70%+ of office job interviews. Master these formulas to work 3x faster and impress your manager.

VLOOKUP — Still the Most Used Formula

=VLOOKUP(lookup_value, table_array, col_index, [range_lookup])

Example: Find salary of employee by ID
=VLOOKUP(A2, EmployeeTable, 3, FALSE)
// A2 = Employee ID, column 3 = salary, FALSE = exact match

Common Errors:
- #N/A = value not found (use IFERROR to handle)
=IFERROR(VLOOKUP(A2,Table,3,0),"Not Found")

INDEX-MATCH — More Powerful Than VLOOKUP

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

// Can look up left (VLOOKUP can't)
=INDEX(A:A, MATCH(D2, C:C, 0))

// Two-criteria match
=INDEX(C:C, MATCH(1, (A:A=D2)*(B:B=E2), 0))
// Ctrl+Shift+Enter for array formula

XLOOKUP — Modern Replacement (Excel 365/2021)

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

=XLOOKUP(A2, Employee_ID, Salary, "Not Found")

// Benefits over VLOOKUP:
// - Can return multiple columns
// - Looks left and right
// - No column number needed
// - Better error handling

SUMIFS — Sum with Multiple Conditions

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)

// Sum sales for Mumbai region, Q1 2024
=SUMIFS(D:D, B:B, "Mumbai", C:C, "Q1")

// Sum sales > 50000 for March
=SUMIFS(D:D, C:C, "March", D:D, ">50000")

Pivot Tables in 5 Steps

1. Select your data range (include headers)
2. Insert → PivotTable → New Worksheet
3. Drag Region to Rows, Product to Columns
4. Drag Sales to Values (Sum by default)
5. Add Date to Filters for time-based analysis

Essential Shortcuts for Indian Offices

• Ctrl+Shift+L — Toggle filters
• Alt+= — AutoSum (most used)
• Ctrl+D — Fill Down
• F4 — Lock reference ($A$1)
• Ctrl+T — Create Table (makes formulas smarter)
• Alt+H+O+I — AutoFit column width