Confident Cells

XLOOKUP and INDEX/MATCH: practice exercises

Modern lookups that can look left. 4 exercises on real-looking data, each marked the moment you press Check. The first exercise is below to try now; the full lesson is part of Pro.

Your turn

In G3, find the ID of the employee named in G2. The ID is to the left of the name, which VLOOKUP can't do.

ABCDEFG
1Staff IDEmployeeTeamOffice
21001GraceSalesR12EmployeeLucy
31002TomFinanceR14Staff ID
41003PriyaSupportS3
51004MarekMarketingS5EmployeeRobin
61005NiaSalesR12TeamNot found
71006OllieFinanceR14
81007LucySupportS3EmployeeRachel
91008HamzaMarketingS5OfficeR12
101009RachelSalesR12
111010SunilFinanceR14
121011TanyaSupportS3
131012WillMarketingS5

How it works

XLOOKUP(what to find, column to search, column to return, if not found) is easier than VLOOKUP. It doesn't need column numbers, it can return values from the left, and it matches exactly by default.

Older versions of Excel don't have XLOOKUP. There, INDEX(column to return, MATCH(what, column to search, 0)) does the same job.

=XLOOKUP(F1,B2:B5,A2:A5) =XLOOKUP(F3,B2:B5,C2:C5,"Not listed") =INDEX(A2:A5,MATCH(F1,B2:B5,0))

What XLOOKUP does

XLOOKUP finds something in one column and returns what's on the same row in another: XLOOKUP(what to find, where to look, what to return, if not found).

It's easier than VLOOKUP: no column numbers, it matches exactly unless you say otherwise, and it can return a column to the left of the one it searches.

Worked example

ABCDEF
1IDNameTeamNameCara
2101AmiraSalesID
3102BenFinanceNameZak
4103CaraSupportTeam
5104DevSales
  1. =XLOOKUP(F1 in . First, what to find: the name in F1, Cara.
  2. =XLOOKUP(F1,B2:B5 in . Then where to look: the Name column.
  3. =XLOOKUP(F1,B2:B5,A2:A5) in . Then what to return: the ID column. It's to the left of the names, which VLOOKUP can't do. Cara's ID is 103.

When it isn't there

ABCDEF
1IDNameTeamNameCara
2101AmiraSalesID
3102BenFinanceNameZak
4103CaraSupportTeam
5104DevSales
  1. =XLOOKUP(F3,B2:B5,C2:C5) in . Zak isn't on the list, so the lookup gives #N/A.
  2. =XLOOKUP(F3,B2:B5,C2:C5,"Not listed") in . The fourth input is what to show instead: Not listed.

INDEX and MATCH

For a grid, INDEX takes a row number and a column number: INDEX(grid, MATCH(…down the side…,0), MATCH(…along the top…,0)).

ABCDEF
1IDNameTeamNameCara
2101AmiraSalesID
3102BenFinanceNameZak
4103CaraSupportTeam
5104DevSales
  1. =MATCH(F1,B2:B5,0) in . Older versions of Excel don't have XLOOKUP. MATCH finds the position of F1 in a column: Cara is 3rd in B2:B5. The 0 means exact match.
  2. =INDEX(A2:A5,MATCH(F1,B2:B5,0)) in . INDEX(column, position) takes the value in that position: the 3rd ID is 103. Same answer as XLOOKUP.

What you'll practise

  1. In G3, find the ID of the employee named in G2. The ID is to the left of the name, which VLOOKUP can't do.
  2. Robin isn't on the list. In G6, look up the team for the name in G5, showing Not found instead of an error.
  3. In G9, find the office for the employee named in G8 using INDEX and MATCH.
  4. In H3, show the sales for the person named in H1 in the month in H2. Use INDEX with two MATCHes: one to find the row, one to find the column.