Excel exercises
Excel exercises: Looking values up
Looking a value up is routine work, and most of what goes wrong sits in the arguments people leave out.
VLOOKUP's fourth argument defaults to approximate match. A formula that works on sorted data can return a neighbour's number on unsorted data. The group teaches both modes first, then HLOOKUP across rows, wildcards for partial keys, and a two-way lookup built from VLOOKUP and MATCH. XLOOKUP gets three exercises: exact match without counting columns, a search from the bottom, and a fallback that replaces IFERROR.
The last exercise puts VLOOKUP and XLOOKUP on one table and compares what each asks you to know. Ten exercises at levels 2 and 3, more than any other group.
Read every formula from the inside out: the value being matched, the block it is matched against, then the column it returns. Almost every wrong answer in this group is the third of those pointing at the wrong column.
A stale exercise is one you finished two weeks ago or longer. One that needs practice took a hint, or more than one try.
0 of 10 complete
- VLOOKUP: exact match Pull a salary out of a table by its ID, with the exact-match flag written out.
- VLOOKUP: approximate match needs a sorted column Paid Read a rate off a band table with approximate match, on a column that rises.
- HLOOKUP: look across a row Paid Pull a price out of a row table by naming the row it sits on.
- VLOOKUP with a wildcard Paid Match a name you half know, and keep the pattern in a cell so it can change.
- Two-way lookup with VLOOKUP and MATCH Paid Return one cell of a grid from a row key and a column key.
- XLOOKUP: exact match, no column counting Paid Return a value from a column anywhere in the table, with no column number to count.
- XLOOKUP: search from the bottom Paid Return the latest entry for a repeated key without sorting the log first.
- XLOOKUP with a fallback when nothing matches Paid Answer a missing SKU with a sentence instead of an error.
- VLOOKUP and XLOOKUP on the same table Paid Read a price with either lookup and know which argument does which job.
- Lookup functions summary Paid Pick the lookup that can return a column to the left of its key.