FALSE for IDs, TRUE only for a sorted band table

The argument nobody types, and what it does

Lesson 6.3 wrote FALSE as the fourth argument and moved on. This lesson is about what happens when it is TRUE — or left out, which is the same thing, because the default is TRUE in both tools (Sheets names the argument is_sorted, Excel range_lookup).

With TRUE, VLOOKUP does an approximate match: it walks down the first column, which it assumes is sorted ascending, and stops at the largest value that is less than or equal to the lookup value. On a table of SKUs that assumption is false and the result is not an error — it is a wrong row, returned with confidence. =VLOOKUP("B200", $H$2:$J$4, 3) might return the right price by luck on a three-row table and the wrong one on a thirty-row table. The rule for IDs, codes and names is therefore fixed: always FALSE.

Worked example — when TRUE is the right tool

Approximate match exists for banding. Maple & Birch grades orders into loyalty tiers by amount: Bronze from $0, Silver from $300, Gold from $700, Platinum from $1,000. Build a two-column table in H2:I5, sorted ascending on the threshold:

threshold  tier
        0  Bronze
      300  Silver
      700  Gold
     1000  Platinum

Then, with amount in D:

=VLOOKUP(D2, $H$2:$I$5, 2, TRUE)

For order 1001 ($420): the largest threshold ≤ 420 is 300 → Silver. For 1005 ($1,320): 1000 → Platinum. For 1007 ($90): 0 → Bronze. Each threshold row reads as "≥ this value, and < the next row's value". Filled down: Bronze 4 · Silver 5 · Gold 2 · Platinum 1. This is Lesson 5.6's IFS without the chain of conditions, and it scales to twenty bands without becoming unreadable — change a threshold in the table and every row updates.

Two failure modes. Shuffle the table — put Gold above Silver — and the walk-down stops in the wrong place, handing $420 orders to Gold or Bronze with no error. And an amount below the first threshold returns #N/A, because no row is ≤ it; here the first row is 0, so nothing can fall below it, but a table starting at 300 would return #N/A for every order under $300 — a below the first band #N/A, not a not found one.

The R side: cut() again

The same tiers are one cut() call with the thresholds as breaks, Inf closing the top and right = FALSE so that each interval is [threshold, next) — exactly the ≥ reading of the band table:

> orders$tier <- cut(orders$amount, breaks = c(0, 300, 700, 1000, Inf),
+                    labels = c("Bronze", "Silver", "Gold", "Platinum"), right = FALSE)
> table(orders$tier)

  Bronze   Silver     Gold Platinum 
       4        5        2        1 

Five breaks, four labels. findInterval(orders$amount, c(0, 300, 700, 1000)) is the closer twin of approximate VLOOKUP — it returns the row number of the band, 2 1 3 1 4 … — but cut gives the labelled factor a pivot or table() wants.

Type both, then fill the tier worksheet

Type the approximate VLOOKUP and the cut. The worksheet asks for the four tier counts.

CRISP-DM: banding a numeric variable is data preparation → construct data.