IntermediateLookup & Reference3 min20 points

When can VLOOKUP not do the job?

The situation

A colleague insists VLOOKUP can handle any lookup and that XLOOKUP is a fashion. Before you argue, it is worth being precise about the one thing VLOOKUP actually cannot do, rather than the things it merely does awkwardly.

The lookup table has more than a thousand rows.
The value you want to return sits to the left of the column you are searching.
The lookup value appears more than once in the table.
The table is on a different worksheet.

Functions in this exercise

VLOOKUP — Finds a value in the first column of a table and returns a value from another column.
XLOOKUP — Finds a value in one range and returns the matching value from another — in any direction.

Who this is for

Data Analyst
Finance
Business Analyst

Recommended next

IntermediateLookup & Reference

Look up a customer's region with XLOOKUP

Match a customer ID against a reference table to return their region.

XLOOKUP

8 min25 points

IntermediateLookup & Reference
Find a value with INDEX and MATCH
Combine INDEX and MATCH to look up a value without a fixed column number.
INDEX · MATCH
10 min25 points
IntermediateLookup & Reference
Look up a customer's region with XLOOKUP
Match a customer ID against a reference table to return their region.
XLOOKUP
8 min25 points
IntermediateLookup & Reference
Look up a unit price with VLOOKUP
Use VLOOKUP with exact match to pull prices from a product table.
VLOOKUP
8 min25 points

When can VLOOKUP not do the job?

The situation

Functions in this exercise

Who this is for

Recommended next

Look up a customer's region with XLOOKUP

Related exercises

Find a value with INDEX and MATCH

Look up a customer's region with XLOOKUP

Look up a unit price with VLOOKUP