Thinkabara

📗 Excel & Google Sheets for Students · Lesson 4 of 7

XLOOKUP and VLOOKUP

Google Classroom

Pull matching data from another table.

The problem

You have a gradebook with student IDs and a separate list of IDs and email addresses. Lookups bring the matching email into the gradebook automatically — no copy and paste.

XLOOKUP

=XLOOKUP(A2, Roster!A:A, Roster!C:C, "Not found")

Look up A2 in the roster’s ID column and return the value from the email column. The 4th argument is what to show if there’s no match.

VLOOKUP

=VLOOKUP(A2, Roster!A:C, 3, FALSE)

Look up A2 in the first column of A:C and return the 3rd column. FALSE = exact match — almost always what you want.

Common errors

#N/ANo match — check spelling, extra spaces (use TRIM) or numbers stored as text
Wrong columnVLOOKUP’s column number counts from the left of the range
Approximate matchForgetting FALSE in VLOOKUP gives wrong results on unsorted data
Lookup to the leftVLOOKUP can’t; XLOOKUP can

Tip

💡 Lookups are perfect for grading scales too: a small table of 0, 60, 70, 80, 90 → F, D, C, B, A and an approximate-match XLOOKUP.

✏️ Practice: your turn

  1. Make two small tables: IDs with names, and IDs with scores
  2. Bring names into the score table with XLOOKUP
  3. Do the same with VLOOKUP and FALSE
  4. Cause a #N/A on purpose and fix it

✏️ Online spreadsheet: practice formulas without installing anything

🧠 Quick quiz

Answer all 4 questions correctly to mark this lesson as done.

1. What does a lookup do?
2. In VLOOKUP, FALSE means…
3. #N/A from a lookup usually means…
4. Which can look up to the left?

0 of 4 correct

All lessons in Excel & Google Sheets for Students

  1. 🎓 1. A grade tracker with weighted averagesSUMPRODUCT, absolute references and “what do I need on the final?”
  2. 🧪 2. Cleaning and formatting lab dataTables, number formats, rounding, sorting and filtering
  3. 🔀 3. IF, COUNTIF and AVERAGEIFLabel results and summarize by condition
  4. 🔍 4. XLOOKUP and VLOOKUPPull matching data from another table
  5. 🧮 5. Pivot tablesSummarize hundreds of rows in a few clicks
  6. 📊 6. Charts for reportsPick the right chart, label it and keep it clean
  7. 💵 7. Project: a student budgetIncome, expenses, categories, a pivot and a chart