INDEX & MATCH Formula in Excel & Google Sheets

Before XLOOKUP, INDEX MATCH was the undisputed champion of spreadsheet lookups. It remains a mandatory skill for financial analysts and works across every spreadsheet program ever created.

---

1. How the Combination Works

• MATCH(lookup_value, lookup_range, 0): Finds where the item is and returns its row number (e.g., row 3).
• INDEX(return_range, row_number): Fetches the value at that specific row number in the target column.

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

---

2. Practical Left-Lookup Example

Employee ID (Col A)	Department (Col B)	Employee Name (Col C)	Salary (Col D)
EMP-01	Sales	Sarah Connor	$82,000
EMP-02	Engineering	John Smith	$110,000
EMP-03	Product	Lisa Wang	$105,000

To lookup Employee Name (Col C) given Employee ID (Col A):

=INDEX(C2:C4, MATCH("EMP-02", A2:A4, 0))

• Returns: John Smith

---

3. Two-Way Matrix Lookup (Row & Column Intersection)

=INDEX(B2:E10, MATCH("Widget A", A2:A10, 0), MATCH("March", B1:E1, 0))