Spreadsheet Practice

Google Sheets lesson 3 of 11, Sheets essentials

VLOOKUP and XLOOKUP in Google Sheets

Pull a name, a price or a rate from another table by its code.

FreeSheets mode10 minutes, 3 exercisesVLOOKUPXLOOKUPIFNAINDEXMATCH

Watch it done

4 steps

Watch the technique worked out on sample data, then try it yourself in the exercises.

How it works

VLOOKUP finds a value in the first column of a range and returns the cell a set number of columns to the right: =VLOOKUP(A2, $F$2:$H$7, 3, FALSE). In Google Sheets the last argument is called is_sorted, and it defaults to TRUE, which means an approximate match on a sorted list. For codes, names and IDs, always pass FALSE.

XLOOKUP is available in Google Sheets too. You point at the column to search and the column to return, so there is no column number to count, and the return column can sit to the left of the search column: =XLOOKUP(K5, $G$2:$G$7, $F$2:$F$7). A fourth argument sets what to show when nothing matches.

A missing code returns #N/A. Wrap a VLOOKUP in IFNA to show a message instead: =IFNA(VLOOKUP(...), "Not found"). IFNA only catches #N/A, so a broken reference or a typo still shows its own error.

INDEX and MATCH do the same job in two steps. MATCH finds the position of the value, and INDEX returns the cell at that position in another column: =INDEX($H$2:$H$7, MATCH(K5, $G$2:$G$7, 0)). You'll see the pair in older sheets and in files shared with Excel users.

Syntax

=VLOOKUP(A2,$F$2:$H$7,2,FALSE)Exact match, second column of the table
=XLOOKUP(A2,$F$2:$F$7,$H$2:$H$7)Search one column, return another
=XLOOKUP(K5,$G$2:$G$7,$F$2:$F$7,"None")Return a column to the left, with a fallback
=IFNA(VLOOKUP(K2,$F$2:$H$7,2,FALSE),"Not found")A message instead of #N/A
=INDEX($H$2:$H$7,MATCH(K5,$G$2:$G$7,0))The two-step version

Watch out for

  • Leaving off FALSE makes VLOOKUP assume the first column is sorted. On unsorted codes it can return the wrong row with no error.
  • Lock the lookup table with $ before filling down, or the range slides off the table one row at a time.
  • VLOOKUP's column number counts from the first column of the range, not from column A.
  • IFERROR hides every error, including a broken reference. Around a lookup, IFNA is the safer choice.

The exercises

  1. Item names with VLOOKUP. In C2, use VLOOKUP to bring back the item name for the SKU in A2 from the price list in F2:H7. Lock the table, then fill down to C6.
  2. Prices with XLOOKUP. In D2, use XLOOKUP to return the price for the SKU in A2: search the SKUs in F2:F7 and return from H2:H7. Fill down to D6.
  3. Missing codes and left lookups. In K3, return the item name for the SKU in K2 with VLOOKUP, and show "Not found" when the SKU isn't in the list. In K6, use XLOOKUP to return the SKU for the item in K5, showing "No such item" when it's missing. In K7, return that item's price with INDEX and MATCH.

The practice grid runs in Sheets mode: FILTER, SORT, QUERY, ARRAYFORMULA, SPLIT and the REGEX functions behave as they do in Google Sheets, and empty results stay blank.

Item names with VLOOKUP

In C2, use VLOOKUP to bring back the item name for the SKU in A2 from the price list in F2:H7. Lock the table, then fill down to C6.