Lookups, FILTER and UNIQUE in Google Sheets

Most Sheets screenings include at least one lookup question and one question about pulling a subset of data. These four functions cover nearly all of them, and the mistakes people make with them are very predictable.

VLOOKUP and its limits

VLOOKUP searches the first column of a range and returns a value from a column to its right.

=VLOOKUP(E2, A2:C200, 3, FALSE)

The 3 means the third column of the range, not column C of the sheet. The FALSE forces an exact match. Leave it out and Sheets assumes sorted data and an approximate match, which can silently return the wrong row. VLOOKUP also cannot look left: if the code is in column D and the price in column B, it will not work.

XLOOKUP as the cleaner option

XLOOKUP takes the search key, the column to search and the column to return, so direction does not matter.

=XLOOKUP(E2, D2:D200, B2:B200, "Not found")

The fourth argument replaces #N/A with your own text. When a lookup still fails, check for extra spaces with TRIM and for numbers stored as text. These cause most #N/A errors that look impossible.

FILTER and UNIQUE

FILTER returns every row that meets a condition. You can combine conditions by multiplying them for AND or adding them for OR.

=FILTER(A2:D100, C2:C100="Paid", D2:D100>500)

UNIQUE returns each distinct value once, which is a quick way to build a client list or the source for a dropdown. Both functions spill their results into the cells below and to the right. If any of those cells contain data, you get a #REF! error, so keep the spill area empty.

Locking references

When you copy a formula down, references move with it. Use $ to stop that. $C$1 stays fixed in both directions, C$1 fixes only the row. A common test question shows a tax rate in one cell that breaks after the first row because it was not locked.

A quick checklist

• Exact match set on every VLOOKUP.
• Ranges the same size in XLOOKUP and FILTER.
• Fixed cells locked with $.
• Spill area left empty.