Course contents · Excel functions

Looking up values

Open the practice in the trainer →

Lookup functions pull data from a reference table: the price for an item code, the name for an employee ID.

VLOOKUP(value, table, col_index, FALSE)searches the first column of the table and returns the value from the given column. FALSE means exact match
XLOOKUP(value, lookup_range, return_range, [if_not_found])modern replacement for VLOOKUP (Excel 2021/365): searches any column, exact match by default, your own text instead of #N/A
MATCH(value, range, 0)the position of a value in a range
INDEX(range, position)the value at a position. INDEX + MATCH works in every Excel version
LARGE(range, k)the k-th largest value (for a "top 3"); its pair is SMALL

Lock the VLOOKUP table with $ signs, otherwise it shifts when you copy the formula down.

Tasks in the trainer

  1. In G2:G4 find the item name for the code in column F.
  2. In H2:H5 find the price; if the code is not in the price list, show "none".
  3. In K1 find the position of "Marker" in the item list.
  4. In K2 show the name of the item with the most stock.
  5. In K3 find the second highest price.

Solve the tasks in the trainer →