Free demo · Google Sheets · Lesson 6 · Intermediate · Lookups

XLOOKUP: find a value in a list

The fee for each course is in a price list. Tell XLOOKUP what to find, where to look and what to bring back, and nobody types a fee by hand.

fx=XLOOKUP(search_key, lookup_range, result_range, [missing_value])
Child | Course | Fee | | Course | Fee Asha | Art | =XLOOKUP(B2,$E$2:$E$5,$F$2:$F$5,"Not on list")→1500 | | Art | 1500 Kabir | Sport | =XLOOKUP(B3,$E$2:$E$5,$F$2:$F$5,"Not on list")→1200 | | Computers | 1800 Zoya | Computers | =XLOOKUP(B4,$E$2:$E$5,$F$2:$F$5,"Not on list")→1800 | | Music | 1400 Diya | Music | =XLOOKUP(B5,$E$2:$E$5,$F$2:$F$5,"Not on list")→1400 | | Sport | 1200 Arjun | Chess | =XLOOKUP(B6,$E$2:$E$5,$F$2:$F$5,"Not on list")→Not on list

The older way: VLOOKUP

=VLOOKUP(B2,$E$2:$F$5,2,FALSE)

  • 2 = answer from the second column of the list
  • FALSE = exact match: always type it
  • It only looks to the right of the first column
  1. 1What to find: the course in B2
  2. 2Where to look: the course names, E2:E5
  3. 3What to bring back: the fees, F2:F5
  4. 4Type it in C2; the $ signs lock the price list
  5. 5Enter: Art is found, the answer is 1500
  6. 6Fill down: Chess gives Not on list, not an error
  7. ✓Older sheets use VLOOKUP: same job, more to remember
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. Enrol

Tip: press → or Space to step through, ← to go back, R to replay.

About this lesson

A lookup formula fetches a value from a list, so that nobody has to type it by hand. In Google Sheets the modern lookup function is XLOOKUP: you give it the search key (what to look for), the lookup range (the column to search in) and the result range (the column to answer from). In this lesson =XLOOKUP(B2,$E$2:$E$5,$F$2:$F$5,"Not on list") finds each child's course in a price list and returns the fee. The match is exact by default, the list does not have to be sorted, and the optional fourth argument replaces the #N/A error with your own message when a course is missing. The dollar signs keep the price list in place when the formula is filled down. The older VLOOKUP does the same job with a column number and FALSE for an exact match, and you will still meet it in many sheets. The full course goes on to VLOOKUP in detail, INDEX with MATCH, lookups across sheets and files with IMPORTRANGE, and IFERROR and IFNA.

Key points

  • XLOOKUP: what to find, where to look, what to bring back
  • Exact match by default, with your own message for a missing value
  • VLOOKUP does the same with a column number and FALSE