Excel exercises
Excel exercises: INDEX and MATCH
INDEX and MATCH are two ordinary functions that combine into a lookup you can point in any direction.
MATCH returns the position of a value in a range, and INDEX returns the value at a row and column. Put those together and you can read left of the key column, which VLOOKUP cannot do. The two ranges are chosen separately, so no column count can drift. The group builds them one at a time so the combination arrives as a conclusion.
Later exercises cover the two-way lookup, with MATCH on rows, MATCH on columns and INDEX at the intersection. Six exercises at levels 2 and 3.
The zero in the third MATCH argument is the exact match, and leaving it out is the mistake that survives testing. A match type of one finds the nearest value below the key and returns that row's value. On an unsorted column that row can belong to a different key.
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 6 complete
- INDEX + MATCH: lookup either way Paid Return a value from any column, whichever side of the key it sits on.
- MATCH: find a value's position Paid Find where a value sits in a list, counting the first cell as 1.
- INDEX: a value at a row and column Paid Read any single cell of a grid from a row number and a column number.
- INDEX MATCH: look left of the key Paid Return a value from the left of the key with INDEX and MATCH.
- Two-way INDEX MATCH Paid Read one cell out of a grid by matching its row and column labels.
- INDEX and MATCH summary Paid Read a rate from a sorted band table with INDEX and an approximate MATCH.