Lookup value, table, column index, match type

Bringing a value in from another table

Maple & Birch keeps a products sheet — sku, product, unit_price — and a lines sheet of order lines with a sku and a qty but no price. Pricing each line means looking its SKU up in products and copying the price across. That is a lookup, and VLOOKUP (vertical lookup) is the function every quiz assumes you know by heart.

Worked example — the price for each line

products sits in H2:J4:

sku   product        unit_price
A100  Ledger binder          24
B200  Desk lamp              60
C300  Office chair          180

lines has five rows, line_id in A, sku in B, qty in C. In D2:

=VLOOKUP(B2, $H$2:$J$4, 3, FALSE)

Four arguments, always in this order:

1. lookup_value — B2, the SKU to find. Relative, so it changes on the fill.
2. table_array — $H$2:$J$4, the whole table. Absolute, because every copy must search the same table; without the $ the range slides down one row per line and the bottom lines search past the end of products.
3. col_index — 3: return the value from the third column of the table array, counting the table's own first column as 1. It is not column J's letter and not its position in the sheet.
4. match type — FALSE: an exact match. Sheets calls this argument is_sorted, Excel range_lookup; both mean the same thing and both default to TRUE, which Lesson 6.4 shows is a trap.

VLOOKUP searches the first column of the table for the value and returns the cell col_index columns across in the same row. Filled down: A100 → 24, C300 → 180, B200 → 60, A100 → 24 — and the fifth line, SKU D400, returns #N/A. #N/A is not "an empty cell" and not a broken formula: it is the lookup saying not found — D400 is not in products, and a price for it does not exist. That is useful information (a product code typo, or a product missing from the master list), and Lesson 6.5 shows how to catch it.

The line total is then a derived column: =C2*D2, or in one step =C3*VLOOKUP(B3, $H$2:$J$4, 3, FALSE) for line 2 — 4 × 180 = 720.

The R side: match() as a row index

R's match(x, table) returns, for each element of x, the position of its first match in table — or NA:

> match(lines$sku, products$sku)
[1]  1  3  2  1 NA
> lines$price <- products$unit_price[match(lines$sku, products$sku)]
> lines$line_total <- lines$qty * lines$price
> lines
  line_id  sku qty price line_total
1       1 A100   2    24         48
2       2 C300   4   180        720
3       3 B200   1    60         60
4       4 A100   5    24        120
5       5 D400   3    NA         NA

The positions index the unit_price column, and the NA propagates into the price and the total exactly as #N/A does in the sheet. Lesson 6.6 does the same job with merge(), which is the general form.

Type both, then price line 2

Type the VLOOKUP and the R match line. The numeric item asks for line 2's total.

CRISP-DM: a lookup that brings a column in from another table is data preparation → integrate data.