Spreadsheets · Level 4 of 5 · Advanced

Lookups and pivot tables

XLOOKUP, VLOOKUP, and summarizing data with pivot tables.

Study notes

VLOOKUP finds a value in the first column of a range and returns a value from another column. Use FALSE for an exact match.
XLOOKUP (Excel 365 and Google Sheets) searches any column and returns from any column, and defaults to an exact match.
INDEX and MATCH together do flexible lookups in older versions.
A pivot table summarizes data by groups, such as total sales by region and month, without writing formulas.
Keep source data in a clean table so lookups and pivots update correctly when you add rows.

Quiz: 6 questions

Choose one answer for each question, then check your answers. Score 5 of 6 to pass. Then enter your name and email to download a PDF certificate and have a copy emailed to you. Answers are graded in your browser.

1. In =VLOOKUP(E2,A:C,3,FALSE), what does FALSE do?

Requires an exact match Returns an error always Sorts the data Returns the first column

2. VLOOKUP searches which column of the range?

The first column The last column Any column Only column Z

3. A pivot table is best for:

Summarizing totals by group, such as sales by region Spell checking Drawing pictures Sending email

4. XLOOKUP's default match type is:

Exact match Approximate match Random No match

5. INDEX and MATCH are often used because:

They can look up in any direction They are colorful They delete data They only work on text

6. New rows are not showing in the pivot. Likely cause?

The pivot's source range does not include them or needs a refresh Pivots never update The font changed Too many sheets

About this certificate: Certificates are issued by Get Certified for Free for passing a free online knowledge quiz. They are not accredited, licensed, government, academic, or professional credentials and do not confer a license. Read the full terms.