First-occurrence tracking with SCAN & LAMBDA (And how to fix the blank row bug)

Hey everyone,

Just wanted to share a formula setup that completely changes how you track sequences, especially if you’re tired of messy old-school helper columns.

We all know UNIQUE is great for telling you what is distinct in a dataset, but it doesn’t tell you when something showed up for the first time chronologically. If you have a long list of transactions, logs, or customer check-ins and you want to flag the exact moment a value debuts - row by row, in order - you need a running unique index.

A lot of examples online tell you to use a basic SCAN and COUNTIF combo with a custom range. Except there is a huge catch: Excel doesn't actually accept that raw variable as a valid range index inside COUNTIF, and if your range has blank rows at the bottom, the counter gets totally confused and starts indexing the empty spaces.

Here is the clean, bulletproof version that handles expanding ranges and ignores blank rows perfectly:

=LET( rng, A2:A100, SCAN(0, SEQUENCE(ROWS(rng)), LAMBDA(acc, idx, LET( current_val, INDEX(rng, idx), sub_range, TAKE(rng, idx), IF(current_val="", "", IF(COUNTIF(sub_range, current_val)=1, acc+1, acc)) ) )) ) 

Instead of feeding the raw text data directly into SCAN, this setup feeds it a row index using SEQUENCE and ROWS. Inside the LAMBDA, TAKE creates a dynamic, expanding slice of your data that grows row by row, looking from the very top down to where the formula is currently working. COUNTIF checks that specific slice. If it’s the first time the value pops up, the counter ticks up. If it’s a duplicate, it holds flat.

The real lifesaver here is the blank check at the end. If your data stops at row 50 but your range goes to 100, it leaves the remaining rows completely blank instead of filling them with ghost numbers.

This comes in handy for a ton of real-world scenarios. In a CRM system, you can use it to pinpoint the exact transaction row where a customer ID converted for the first time. If you do log analysis, it lets you flag the exact timestamp a specific error code made its first appearance. It is also great for inventory management when you need to mark the precise row a new SKU debuted in a seasonal order list.

The best part is that it lives in a single cell, it is fully dynamic, and the index updates automatically as you add rows. Just a heads-up: since it uses COUNTIF on an expanding range inside SCAN, it can get heavy if you throw 50k rows at it, but for standard datasets it works like a charm.

SCAN has been around since 2022 but it feels like barely anyone touches it. Definitely worth adding to your toolkit if you want to level up your spreadsheet game.

Note; content tested and verified on Excel 365.