Lookups and combining data

Lookups bring data from one table into another, such as an employee's department, a product's price or a city's region.

VLOOKUP (still common in interviews)

=VLOOKUP(A2, Products!A:D, 3, FALSE)

Looks up A2 in the first column of Products!A:D and returns the 3rd column. FALSE means exact match (almost always what you want). Limitations: it can't look to the left, and the column number breaks if columns are inserted.

INDEX + MATCH (flexible and robust)

=INDEX(Products!C:C, MATCH(A2, Products!A:A, 0))

MATCH finds the row; INDEX returns the value from any column, including columns to the left.

XLOOKUP (Excel 365/2021; Google Sheets has it too)

=XLOOKUP(A2, Products[Code], Products[Price], "Not found")

Searches any direction, exact match by default, built-in "not found" handling, can return multiple columns.

Two-way lookup

=INDEX(Data, MATCH(Region, Regions, 0), MATCH(Month, Months, 0))

Handling errors

#N/A means not found (check for extra spaces with TRIM, numbers stored as text, or genuine missing values). Wrap lookups in IFERROR(…, "Missing") only after you've understood why values are missing.

Modern dynamic array functions

UNIQUE (distinct values), SORT, FILTER (rows matching conditions), SEQUENCE. Example: =FILTER(Orders, Orders[Status]="Pending") returns all pending orders, updating automatically.

Check yourself

• Why does VLOOKUP return a wrong value when the last argument is omitted?
• Write an XLOOKUP that returns an employee's manager from an Employees table.
• A lookup returns #N/A although the value is visibly there. What could be wrong?