#259 Excel & Analytics Beginner 🕑 10 min read

Excel Lookup Formulas for Accountants: VLOOKUP, XLOOKUP, INDEX-MATCH & More

Lookup formulas are the foundation of accounting data work in Excel — from reconciling GST returns against purchase registers to mapping old chart of accounts codes to a new system. This guide covers VLOOKUP, XLOOKUP, INDEX-MATCH, and XMATCH with syntax, common errors, and India-specific accounting use cases including GST reconciliation, payroll, and TDS rate lookups.

Why Lookup Formulas Matter for Accountants

Every accounting reconciliation in India involves matching data from two or more sources. Your purchase register from Tally needs to match the GSTR-2B download from the GST portal. Your payroll master needs to pull employee names and designations from HR's system. Your bank statement needs to be categorized against your chart of accounts. All of these tasks rely on one skill: lookup formulas.

Accounting Task	Lookup Use Case
GST Reconciliation	Match GSTIN + Invoice Number from GSTR-2B to purchase register
Payroll Processing	Pull employee details (PAN, bank account, grade) from master file using employee code
TDS Calculation	Look up TDS rate from section-based rate table using vendor category
Bank Reconciliation	Match bank statement reference number to GL transaction reference
Chart of Accounts Migration	Map old account codes to new account codes during ERP migration
Budget Mapping	Pull approved budget amounts against actual expense line items

Career Insight: In every accounting and finance interview in India — from Big 4 to MNC finance teams — VLOOKUP and INDEX-MATCH are the most commonly tested Excel skills. Being fluent in XLOOKUP (Excel 365) additionally marks you as current with modern Excel practices.

VLOOKUP: Syntax, Usage & Limitations

VLOOKUP Syntax Explained

VLOOKUP Syntax =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value → What you're searching for (e.g., employee code A101)
table_array → The range containing your lookup table (e.g., EmployeeMaster!A:D)
col_index_num → Which column in the table to return (1=first col, 2=second, etc.)
range_lookup → TRUE = approximate match, FALSE = exact match

RULE: Always use FALSE (exact match) for accounting lookups
=VLOOKUP(A2, EmployeeMaster!$A:$D, 3, FALSE) ✓ Correct
=VLOOKUP(A2, EmployeeMaster!$A:$D, 3, TRUE) ✗ Wrong — finds nearest match (dangerous in reconciliations)

When range_lookup = TRUE Goes Wrong

TRUE (approximate match) is designed for sorted numeric ranges like tax bracket lookups. It finds the largest value less than or equal to your lookup value. If you accidentally use TRUE for account code lookups, VLOOKUP will silently return the wrong result for any code not found — instead of showing an error, it returns the nearest match. In a reconciliation context, this means wrong amounts with no error flag: the worst possible Excel failure mode.

VLOOKUP Limitations Every Accountant Must Know

VLOOKUP Can Only...

Look in the leftmost column of the table_array
Return values to the right of the lookup column
Reference one lookup value at a time
Return one column per formula

VLOOKUP Cannot...

Look left (return data from a column to the LEFT of the lookup column)
Handle column insertions — col_index_num breaks when columns are inserted
Match on multiple criteria without a helper column
Return multiple columns in one formula

VLOOKUP Error Reference

#N/ALookup value not found. Check for extra spaces (use TRIM), number-text mismatch (use VALUE()), or the value genuinely doesn't exist in the table.

#REF!col_index_num exceeds the number of columns in table_array. Fix: reduce col_index_num or expand the table_array range.

#VALUE!col_index_num is less than 1 or is not a number. Also appears if lookup_value or table_array arguments are wrong data types.

Wrong result (no error)range_lookup is TRUE when it should be FALSE. Excel finds the nearest match without warning. Always specify FALSE explicitly.

Handling #N/A Gracefully with IFERROR =IFERROR(VLOOKUP(A2,Budget!$A:$C,3,FALSE),"Not in Budget") → Returns text message
=IFERROR(VLOOKUP(A2,Budget!$A:$C,3,FALSE),0) → Returns 0 (for numeric calculations)
=IFNA(VLOOKUP(A2,Budget!$A:$C,3,FALSE),0) → IFNA only catches #N/A (better practice)

// Prefer IFNA over IFERROR — IFERROR hides ALL errors including real formula mistakes

HLOOKUP for Horizontal Tables

HLOOKUP Syntax =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

// Same logic as VLOOKUP but searches across the top ROW instead of leftmost column
// Useful when your reference table has dates across columns (monthly budget header row)
// Example: Look up April budget row from a 12-month horizontal budget table
=HLOOKUP("Apr-25", BudgetTable, 3, FALSE) → Returns row 3 value for April

INDEX-MATCH: The Professional's Lookup

INDEX-MATCH is a combination of two separate functions that together overcome every limitation of VLOOKUP. It is the preferred lookup method among experienced Excel users in accounting and finance — and the answer your interviewer is hoping to hear when they ask "what's better than VLOOKUP?"

Understanding Each Function Separately

INDEX Function =INDEX(array, row_num, [col_num])
// Returns the value at a specific position in a range
=INDEX(C2:C100, 5) → Returns the 5th cell in C2:C100 (i.e., C6)
=INDEX(A2:D100, 3, 2) → Returns row 3, column 2 of the range (i.e., B4)

MATCH Function =MATCH(lookup_value, lookup_array, [match_type])
// Returns the POSITION (row number) of a value in a range, not the value itself
=MATCH("A101", A2:A100, 0) → Returns 5 if "A101" is in the 5th row of the array
match_type: 0 = exact match (always use 0 in accounting), 1 = less than, -1 = greater than

Combining INDEX and MATCH

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

// To get Employee Name from an Employee Master using Employee Code:
=INDEX(EmployeeMaster!C:C, MATCH(A2, EmployeeMaster!A:A, 0))
// A:A = Employee Code column (lookup column)
// C:C = Employee Name column (return column — can be LEFT of A:A — VLOOKUP cannot do this)

// VLOOKUP equivalent (only works if return column is to the RIGHT):
=VLOOKUP(A2, EmployeeMaster!A:C, 3, FALSE) → limited
// If you need a column to the LEFT (like column B from a lookup on column D):
=INDEX(EmployeeMaster!B:B, MATCH(A2, EmployeeMaster!D:D, 0)) → works perfectly

INDEX-MATCH with Multiple Criteria

When you need to match on two or more criteria simultaneously (e.g., match both GSTIN AND invoice number), INDEX-MATCH with an array formula is the solution:

Multi-Criteria INDEX-MATCH (Array Formula) // Match on both GSTIN (column A) AND Invoice Number (column B) to return Amount (column C)
=INDEX(C:C, MATCH(1, (A:A=E2)*(B:B=F2), 0))
// Enter with Ctrl+Shift+Enter in older Excel (shows {braces}), or just Enter in Excel 365

E2 = GSTIN you're looking for
F2 = Invoice Number you're looking for
(A:A=E2)*(B:B=F2) = creates an array of 1s (where BOTH conditions match) and 0s
MATCH finds the position of the first 1 (i.e., first row where both match)
INDEX returns the Amount from that row

Column Insertion Advantage: A critical INDEX-MATCH advantage in live accounting models — if someone inserts a column between your lookup column and return column, VLOOKUP's col_index_num becomes wrong (silently). INDEX-MATCH references actual column ranges, so it always returns the correct column regardless of insertions.

XLOOKUP: The Modern Standard (Excel 365 & 2021)

XLOOKUP (released in 2019) combines the best features of VLOOKUP, HLOOKUP, and INDEX-MATCH into a single, cleaner function. If your organization uses Excel 365 (Microsoft 365 subscription), always prefer XLOOKUP over VLOOKUP.

XLOOKUP Syntax

XLOOKUP Full Syntax =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

lookup_value → What you're searching for
lookup_array → The single column/row to search in
return_array → The column(s) to return (can be multiple columns!)
if_not_found → Value to return when not found (replaces IFERROR wrapper)
match_mode → 0=exact (default), -1=exact or next smaller, 1=exact or next larger, 2=wildcard
search_mode → 1=first-to-last (default), -1=last-to-first, 2=binary ascending, -2=binary descending

Basic XLOOKUP Example =XLOOKUP(A2, EmployeeMaster!A:A, EmployeeMaster!C:C, "Not Found")
// Cleaner than: =IFERROR(VLOOKUP(A2,EmployeeMaster!A:C,3,FALSE),"Not Found")

XLOOKUP Advantages Over VLOOKUP

Feature	VLOOKUP	XLOOKUP
Look left (return column before lookup)	No	Yes
Built-in error handling	Needs IFERROR wrapper	if_not_found parameter
Return multiple columns at once	No (one formula per column)	Yes (specify multi-column return_array)
Reverse search (last match)	No	search_mode = -1
Binary search for performance	Approximate match only	search_mode = 2 or -2
Horizontal lookup (replaces HLOOKUP)	No (need HLOOKUP)	Yes (works on rows too)
Column insertion resilience	Breaks (col_index_num is fixed)	Uses range references, not numbers

XLOOKUP Special Use Cases

Return Multiple Columns at Once // Return Employee Name, Department, AND Grade in one formula (spills across 3 columns)
=XLOOKUP(A2, EmployeeMaster!A:A, EmployeeMaster!C:E, "Not Found")
// C:E = columns C, D, E returned simultaneously. Enter in one cell — fills adjacent cells automatically.

Reverse Lookup: Find Last Occurrence // Find the most recent invoice from a vendor (when same vendor appears multiple times)
=XLOOKUP(A2, VendorLedger!A:A, VendorLedger!C:C, "No invoices", 0, -1)
// search_mode = -1 searches from last row to first row → returns the LAST match

Wildcard Matching with XLOOKUP // Match partial account names (e.g., find any account containing "Salary")
=XLOOKUP("*Salary*", AccountNames, AccountCodes, "Not found", 2)
// match_mode = 2 enables wildcard (* and ?) matching

XMATCH: Positional Lookups

XMATCH Syntax =XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

// Returns the POSITION number, not the value — pair with INDEX for full lookup
=XMATCH("Sales", SheetHeaders, 0) → Returns column position of "Sales" in header row

// Dynamic column reference — instead of hardcoding col_index_num in VLOOKUP:
=INDEX(DataRange, ROW()-1, XMATCH("Sales Revenue", HeaderRow, 0))
// Now if columns are reordered, the formula still finds the right column by name

India Accounting Use Cases

1. GST Reconciliation: GSTR-2B vs Purchase Register

This is the most critical monthly task for any Indian accountant. The reconciliation matches invoices appearing in your GSTR-2B (supplier-uploaded data) against your own purchase register to identify mismatches before filing GSTR-3B.

GST Reconciliation Formula // In your Purchase Register sheet, add columns to pull GSTR-2B data:

// Create a combined key in both sheets: GSTIN + Invoice Number
Helper_Key (Purchase Register) = A2 & "|" & B2 // GSTIN & Invoice No.
Helper_Key (GSTR-2B) = A2 & "|" & B2 // Same concatenation

// Now XLOOKUP on the combined key:
GSTR2B_IGST = XLOOKUP(Helper_Key, GSTR2B!Helper_Col, GSTR2B!IGST_Col, 0)
Difference = [@GSTR2B_IGST] - [@Books_IGST]

// Highlight differences: Conditional format rows where Difference <> 0

2. TDS Rate Lookup: Section & Threshold Based

TDS Rate Table Lookup // TDS Rate Table columns: Section, Nature_of_Payment, Threshold, TDS_Rate_Individual, TDS_Rate_Company
// Lookup TDS rate for a vendor based on their payment section:

TDS_Rate = XLOOKUP([@Section], TDSRateTable[Section],
IF([@Vendor_Type]="Company", TDSRateTable[Rate_Company], TDSRateTable[Rate_Individual]),
"Rate Not Found")

// Check if payment exceeds threshold before applying TDS:
TDS_Amount = IF([@Cumulative_Payment] > XLOOKUP([@Section], TDSRateTable[Section], TDSRateTable[Threshold]),
[@Payment] * TDS_Rate,
0)

3. Employee Payroll: Multi-Field Master Lookup

Payroll Master Lookup Using XLOOKUP // Payroll processing sheet: Employee Code in column A
// Pull Name, Designation, Department, Basic Salary, PAN from HR Master:

// Traditional approach (5 separate VLOOKUPs):
Employee_Name = VLOOKUP(A2, HRMaster!A:G, 2, FALSE)
Designation = VLOOKUP(A2, HRMaster!A:G, 3, FALSE) // etc.

// Modern XLOOKUP approach (one formula, fills 5 columns):
=XLOOKUP(A2, HRMaster!A:A, HRMaster!B:F, "Employee Not Found")
// Returns Name, Designation, Department, Basic, PAN simultaneously

4. Chart of Accounts Migration

Old CoA to New CoA Mapping // During ERP migration (e.g., Tally to SAP), map old account codes to new ones:
New_Account_Code = XLOOKUP([@Old_Account_Code],
CoAMapping[Old_Code],
CoAMapping[New_Code],
"UNMAPPED - Review Required")

// Identify unmapped accounts before go-live:
=COUNTIF(New_Code_Column, "UNMAPPED - Review Required") → Shows count of unmapped codes
=FILTER(CoA_Data, New_Code_Column="UNMAPPED - Review Required") → Lists all unmapped accounts

5. Bank Statement Auto-Categorization

Bank Statement Categorization Using Wildcard XLOOKUP // Keyword mapping table: Keyword → Category
// "NEFT/NEFT RATAN" → "Vendor Payment", "SALARY" → "Payroll", "RENT" → "Office Expenses"

// Exact match first, then try wildcard categories:
Category = XLOOKUP([@Narration], KeywordTable[Keyword], KeywordTable[Category], "Uncategorized", 2)
// match_mode=2 uses wildcard matching: if narration CONTAINS the keyword, it matches

// Note: First partial match wins — order your keyword table from most specific to least specific

Performance Tips & Array Formulas

Performance Optimization for Large Datasets

When working with datasets over 50,000 rows, lookup formula performance becomes noticeable. Apply these techniques to keep your workbook fast:

Limit lookup ranges to used rows — Use A2:A10000 instead of A:A when your data has a known end point. Full-column references (A:A) force Excel to check 1,048,576 rows every time.
Use binary search for sorted data — XLOOKUP with search_mode=2 performs a binary search on sorted data, far faster than sequential scanning. Sort your lookup column ascending and set search_mode to 2.
Convert lookup tables to Excel Tables (Ctrl+T) — Structured table references like Table1[Employee Code] are faster and more readable than cell range references.
Avoid volatile functions in lookup arguments — Functions like TODAY(), NOW(), RAND() in lookup criteria force recalculation on every change, multiplied by the number of lookup rows.
Use IFERROR sparingly — IFERROR evaluates the formula twice if it returns an error. Prefer IFNA (only catches #N/A) or fix the root cause of the error instead.

FILTER + XLOOKUP for Dynamic Results

FILTER Function (Excel 365) for Multi-Row Lookups // Return ALL invoices from a specific vendor (not just the first match like XLOOKUP):
=FILTER(InvoiceTable, InvoiceTable[Vendor_Name]=A2, "No invoices found")

// Return all overdue invoices for a department:
=FILTER(ARTable, (ARTable[Department]=A2) * (ARTable[Days_Overdue]>30))
// * operator = AND logic between conditions

// Combine XLOOKUP with FILTER for cascading lookups:
=SORT(FILTER(InvoiceTable, InvoiceTable[Customer_ID]=XLOOKUP(A2,CustomerNames,CustomerIDs)),1,-1)
// Looks up Customer ID from name, then filters invoices for that customer, sorted descending

⚡ Take Action Now

Practice these lookup formulas on CorpReady's free Indian accounting dataset: 2,000 rows of payroll data, a GST reconciliation worksheet, and a TDS calculation template. Download and work through every use case in this guide.

Explore CorpReady Programs

📚 Real Student Story

Deepika Sharma, CA Inter cleared, Jaipur — Deepika was working at a CA firm handling GST compliance for 35 clients. Every month, the GSTR-2B reconciliation for each client was done manually — comparing printed PDFs against Tally exports, often taking 3-4 days. After learning INDEX-MATCH and XLOOKUP through CorpReady's Excel module, she built a reconciliation template using multi-criteria INDEX-MATCH on GSTIN + invoice number combinations. The template reduced per-client reconciliation time from 2 hours to 15 minutes. "I finished 35 clients in the same time it used to take for 5," she says. The CA firm principal encouraged her to pursue ACCA alongside her CA articleship, and CorpReady's dual-track program made that possible.

💼 What Firms Actually Want

In MNC finance roles (shared service centres in Bengaluru, Chennai, Hyderabad — think Accenture, Genpact, WNS, Infosys BPO), VLOOKUP/XLOOKUP proficiency is a baseline hiring requirement. Candidates who know INDEX-MATCH with multiple criteria are preferred for reconciliation-heavy roles. Big 4 advisory teams test lookup formulas during Excel assessments and expect candidates to handle #N/A errors gracefully using IFERROR/IFNA, clean data with TRIM before lookups, and use absolute references ($) to lock lookup ranges when dragging formulas down. CorpReady's curriculum covers all of this in the context of real accounting datasets used in India.

Frequently Asked Questions

VLOOKUP requires the lookup column to be the leftmost column in the search range and can only return values from columns to the right. XLOOKUP has no such restriction — it searches any column and returns data from any direction, including to the left. XLOOKUP also has a built-in if_not_found parameter (replacing the need for an IFERROR wrapper), can return multiple columns in one formula, supports reverse search, and handles column insertions correctly. XLOOKUP is available in Excel 365 and Excel 2021.

#N/A means the lookup value was not found in the lookup column. The most common causes in accounting work are: extra leading or trailing spaces in one source but not the other (fix with TRIM()), the value is stored as text in one file and as a number in another (fix with VALUE() to convert text to number), inconsistent date formats, or the value genuinely does not exist in the lookup table. Wrap your VLOOKUP in IFERROR or IFNA to return a custom message or zero instead of the error.

Use INDEX-MATCH over VLOOKUP when: (1) you need to look left — the return column is to the left of the lookup column; (2) your data structure involves column insertions that would shift VLOOKUP's col_index_num; (3) you need to match on multiple criteria simultaneously using an array formula; or (4) you're working with very large datasets where INDEX-MATCH with exact match is more efficient than VLOOKUP on unsorted data. In Excel 365, XLOOKUP is the best option for all new work.

Download your GSTR-2B from the GST portal and your purchase register from your accounting system. Create a helper key column in both sheets by concatenating GSTIN and invoice number (e.g., =A2&"|"&B2). In your purchase register, use XLOOKUP on the helper key to pull the ITC amounts from GSTR-2B. Add a difference column (GSTR-2B amount minus books amount). Filter rows where the difference is non-zero — these are reconciling items requiring vendor follow-up or book corrections before filing GSTR-3B.

✅ Key Takeaways

Always use range_lookup = FALSE (exact match) in VLOOKUP for accounting lookups — TRUE (approximate match) silently returns wrong results if the lookup value is not found
INDEX-MATCH overcomes VLOOKUP's two critical limitations: it can look left and never breaks when columns are inserted
XLOOKUP (Excel 365/2021) is the modern standard — it replaces both VLOOKUP and HLOOKUP, handles errors natively, returns multiple columns, and supports reverse search
For GST reconciliation, create a combined key by concatenating GSTIN and invoice number to enable multi-criteria matching in a single lookup column
Wrap lookups in IFNA (not IFERROR) to catch only missing-match errors while still surfacing real formula errors
Use FILTER (Excel 365) when you need all matching rows, not just the first match — critical for audit-trail lookups and multi-invoice vendor analysis

Ready to Master Excel for Finance?

CorpReady Academy's finance programs build Excel mastery alongside globally recognized credentials. Our US CPA, US CMA, ACCA and CFA programs include hands-on Excel labs with real Indian accounting datasets covering GST reconciliation, payroll processing, and financial analysis.

Explore CorpReady Programs Talk to a Counsellor