Join reference information

Attach reference data by exact match, distinguish an unmatched key from a duplicate one and from a key that differs only in how it is stored, and return a value from a column to the left of the key.

A lookup is how two tables become one, and it is the point where a sheet stops being something you can check by reading it. The order list looks right. The rate table looks right. The prices that appear beside the orders came from somewhere, and there is no way to see where by looking.

Three things go wrong, and they fail with three different volumes.

A missing key returns #N/A, which is loud and gets fixed.

A key stored differently — the number 400 in one table, the text "400" in the other — also returns #N/A, which is loud and gets misdiagnosed, because the two values are identical on screen and people conclude the lookup is broken.

A duplicate key returns a perfectly good number and says nothing. The lookup finds the first matching row and stops. If that row is last March's price and there is a second row with August's, the order has been priced at the wrong rate, and the sheet is as confident about it as about everything else.

The third one is the reason this lesson insists on a column that reports what each lookup actually did.

Exact match is not the default, and that is the single most important fact here.

=VLOOKUP(key, table, column number, FALSE)

That fourth argument is what makes the match exact. Leave it out and both Excel and Google Sheets — and this grid, deliberately — do an approximate match instead: they assume the first column is sorted and return the row with the largest key that is less than or equal to yours. On unsorted data the result is arbitrary. It is also a number, so nothing looks wrong.

There is no case in this course, and few outside it, where an omitted fourth argument is what you meant. Write FALSE every time.

A duplicate key returns the first match, silently. Nothing in VLOOKUP, MATCH or XLOOKUP warns you that a second matching row exists. If your reference table can contain duplicates — and any table that people edit over time can — then a lookup on its own is not enough. Count the matches alongside it:

=COUNTIF(reference key column, this key)

More than one means the value you got is one of several, chosen by position rather than by anything meaningful.

Text and numbers never match each other. "400" and 400 are different values, here and in both products, and no amount of formatting changes that. This is the same distinction Module 1 lesson 1 introduced, arriving where it does the most damage. When a lookup fails on a key you can see with your own eyes in the reference table, this is almost always why. Check with ISNUMBER on both sides before assuming the formula is wrong.

VLOOKUP only looks right. It takes a block, finds the key in that block's first column, and returns a value from a column further along. It cannot return anything to the left of the key. When the value you want is on the wrong side — and in any table where the human-readable name comes before the code, it will be — use a method that separates finding from fetching:

=INDEX(column to return from, MATCH(key, column to search, 0))

MATCH answers "which row?", INDEX answers "what is in that row of this column?", and neither cares which side of the key the column is on. That 0 is MATCH's exact-match argument, and it is as necessary as VLOOKUP's FALSE.

XLOOKUP does the same job in one function where it is available, taking the search column and the return column as separate arguments. It is newer, so it is not in every version of Excel, which is why INDEX and MATCH remain worth knowing.

Do not wrap the failure. IFERROR(VLOOKUP(...), 0) turns an unpriced order line into a free one. IFERROR(..., "") turns it into a blank that a total will skip. Both convert "we do not know" into a number that behaves like a fact. If a value is genuinely allowed to be absent, say so in words in a column of its own, count how many there are, and let the total that depends on them refuse to compute.

The rate table, which people have edited over time:

        A        B          C
      Item    Item code  Unit price
row 2 Cones      100        4.25
row 3 Bibs       200        6.10     priced in March
row 4 Nets       300        9.00
row 5 Bibs       200        6.95     re-priced in August; the March row was left
row 6 Balls      400        3.40

Two things about the shape of this table matter before any formula is written.

The item name is in column A and the code is in column B, so a lookup by code cannot use VLOOKUP to fetch the name. The name is to the left.

And code 200 appears twice. A lookup for 200 returns 6.10, because row 3 comes before row 5. Nothing says so. An order for eight bibs is priced at 48.80 when it might have been 55.60, and the 6.80 difference is invisible.

Now the order lines:

O-02  code 200   a duplicate key: priced, and not reliably
O-04  code 500   not in the table at all
O-05  code 400   entered as text; the table holds numbers

O-04 and O-05 both return #N/A and they need completely different fixes: one is a missing rate, the other is a data-entry format.

Using the table above, predict all four.

=VLOOKUP(200,Rates!$B$2:$C$6,2,FALSE)
=VLOOKUP(250,Rates!$B$2:$C$6,2,FALSE)
=VLOOKUP(250,Rates!$B$2:$C$6,2)
=COUNTIF(Rates!$B$2:$B$6,200)

The price lookup, written properly:

=VLOOKUP($B2,Rates!$B$2:$C$6,2,FALSE)

The key is anchored on its column so the formula can be copied across as well as down; the table is anchored on both axes so it does not drift; the 2 is the position of the price column within that block, not on the sheet; and FALSE makes it exact.

The name lookup cannot be a VLOOKUP at all, because the name is left of the key:

=INDEX(Rates!$A$2:$A$6, MATCH($B2, Rates!$B$2:$B$6, 0))

Read it inside out. MATCH searches the code column for this row's code and returns a position — 1, 2, 3 and so on within that range. INDEX takes the name column and returns the value at that position. The two ranges have to be the same height, and they can be in any order on the sheet.

Then the column that makes all of it auditable:

=IF(ISNA(D2), "code not in the rate table",
   IF(COUNTIF(Rates!$B$2:$B$6,$B2)>1, "duplicate code", "matched"))

Three outcomes, in order: the lookup failed; the lookup succeeded but had more than one row to choose from; the lookup succeeded cleanly. Only the third is a result you can use without a caveat.

ISNA rather than ISERROR is deliberate here, though either passes: ISNA catches the specific "no match" error and lets any other error through to be noticed, while ISERROR would quietly absorb a #REF! from a mistyped range as though it were a missing code.

Every price in a supplier order looks plausible and the order total is 11% higher than the same order was last quarter. Nobody changed a price.

1. Count the matches, not the values. Add =COUNTIF(reference key column, this key) beside each line. Any line reporting more than 1 was priced from a shortlist.
2. Look at the reference table's history, not its contents. A duplicate key almost always means somebody added a corrected row and did not remove the old one. Which of the two the lookup finds depends on their order, so re-sorting the reference table — an action nobody would think of as a change — can silently reprice everything.
3. Check the fourth argument on every lookup in the sheet. One omitted FALSE will price items that do not exist, from neighbouring codes. Search the sheet for ,2) and similar closing patterns.
4. Check the key types. If any line returns #N/A for a code you can see in the table, compare ISNUMBER on both sides before touching the formula.
5. Put the exposure in a number. "Two lines are priced from duplicate codes, and the difference between the two candidate prices is 6.80" is a sentence that gets the reference table cleaned. "There might be duplicates" is not.

The general shape: a lookup that returns a value has not told you that the value was the only candidate. Ask for the count as well as the value, every time the reference table is something people edit.

Six order lines and a rate table that has been edited over time. One code appears twice, one is missing, and one looks perfectly fine and will not match.

Build the two lookups, let the errors show, and report what each lookup actually did.

Find two tables in your own work that are joined by a key: a product list and a price list, a staff list and a rota, a postcode and a region.

1. Name the key, and say whether it is stored as text or as a number in each table. If they differ, you have found a fault that has not fired yet.
2. Ask whether the reference table can contain the same key twice. If people edit it, the answer is yes regardless of what anybody intends.
3. Write down what should happen when a key is not found. "Nothing" is not an answer; the alternatives are refuse, flag, or substitute a default, and each is right sometimes.
4. Say who would notice if the join silently attached the wrong row, and how long it would take.

Question 4 is the one that decides how much of this lesson you need. If the answer is "nobody, ever", the count-of-matches column is not optional.

Sign in to track your progress on this exercise.

This module is finished when you can do all of these.

• Say what an omitted fourth argument makes VLOOKUP do, and why that is worse than an error.
• Explain what a lookup returns when the key appears twice, and what to add beside it.
• Say why the text "400" never matches the number 400, and how to check which one a cell holds.
• Write a lookup that returns a value from a column to the left of the key, and name the two functions it uses.
• Give two specific harms caused by wrapping a lookup in IFERROR with a zero.
• Explain why a total over a column containing #N/A is honest but useless, and what to report instead.

Sign in to track your progress on this exercise.

A join is finished when the match is exact by construction, a missing key shows as an error rather than a zero, a duplicate key is counted as well as used, and the sheet says how much of its total it can vouch for.

That completes the third module. You can now turn a written rule into a tested formula, answer a grouped question with the right denominator, and attach a second table without losing what did not attach.

Module 4 changes the question. So far every number has described something that already happened. Next you will build models of things that have not — costs, plans, alternatives — where nothing can be reconciled against reality, and the assumptions are the whole of the answer.