INDEX-MATCH left lookup

Intermediate

It's a common sight: exports where the columns aren’t in the order you wish they were. For example, email appears to the left of customer ID, so a basic VLOOKUP can’t return it.

INDEX + MATCH solves this cleanly:

  • MATCH finds the row number where a customer ID appears
  • INDEX returns the email from that same row

Your task

You have a customer directory in A1:D9, where Email is in column A and Customer ID is in column B.

Fill the missing emails for the campaign tasks:

  1. In C12, write an INDEX-MATCH formula that returns the email for the customer ID in B12.
  2. Copy the formula down through C14.

Use exact match in MATCH, and lock the directory ranges so your formula copies correctly.

Need some help?

Hint 1

MATCH should search the Customer ID column and return a position using exact match (0).

Hint 2

INDEX should return from the Email column, using the row position returned by MATCH.

Hint 3

Lock the directory ranges with $ so they don’t shift when you fill the formula down from C12 to C14.

Related function(s)
INDEXMATCHXLOOKUPVLOOKUPXMATCH
More exercises
XLOOKUP left lookupIntermediate
Look up a manager with INDEX+MATCHIntermediate
Two-way lookupAdvanced
VLOOKUP with approximate matchIntermediate
Back to exercises overview

Exercise