Why does Lookup sometimes append nulls even when a match exists?

General
1

Because first match wins.

Append mode stops scanning lookup rows as soon as it finds a match.
If the match occurs after earlier non-matches, that’s fine.

But if:

  • Whole-word is enabled

  • Or match location is restrictive

Then a visually “obvious” match may never qualify.

Example

Lookup value: US
Input text: RUSSIA

  • Whole-word ON → no match

  • Partial match OFF → no append

Lesson
String matching rules matter more than the data itself.