Excel Intermediate Lesson 3: INDEX/MATCH and Advanced XLOOKUP

📎 This article includes 1 downloadable practice file ↓

📘 Excel Intermediate Course · Lesson 3 of 12

VLOOKUP gets you started. Real reports need more: the target for a region and a month, the code for a product name (to the left), the last invoice of a rep. Two tools cover all of it.

INDEX + MATCH

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

MATCH finds the position, INDEX returns what is at that position. Because the two ranges are separate, the return column can be anywhere, including to the left.

Two-way lookup

Targets for 4 regions × 6 months sit in Targets!B2:G5. The target for West in July:

=INDEX(Targets!B2:G5, MATCH("West", Targets!A2:A5, 0), MATCH("Jul", Targets!B1:G1, 0))
=XLOOKUP("Jul", Targets!B1:G1, XLOOKUP("West", Targets!A2:A5, Targets!B2:G5))

The inner XLOOKUP returns West’s whole row; the outer one picks July from it.

XLOOKUP features VLOOKUP never had

Common mistakes

• MATCH without the 0. The default is an approximate match on sorted data; on unsorted lists it returns wrong rows silently.
• Ranges of different heights in XLOOKUP give #VALUE!.
• Extra spaces in the lookup value. If an obvious match returns #N/A, try TRIM on both sides.

Practice

Download this lesson’s workbook below. Targets are in ₹ thousands. Type your formula in the yellow column; the Check column turns green when the result matches, and the Answers sheet has a working formula for every task.