This is the most common lookup problem, and the formula is almost never the real cause. In nearly every case the two values are not identical in the way Excel compares them.
What causes it
A trailing space on one side
The value in one cell reads "Acme" and the other "Acme " with a space on the end. They look the same but do not match. Wrap both sides in TRIM, or clean the source column with TRIM once and paste it back as values.
A number on one side, text on the other
A code like 01234 may be a real number in one column and text in the other. They will never match. Format both columns the same way, or convert the text one with VALUE.
Approximate match left switched on
A VLOOKUP without a fourth argument, or with TRUE, does an approximate match and needs sorted data. Add FALSE (or 0) as the last argument to force an exact match.
The lookup value is not in the first column
VLOOKUP only searches the first column of its range. If the value you are matching sits in a later column, it cannot find it. INDEX and MATCH, or XLOOKUP, look wherever you point them.
What to do
Start by testing for spaces: put =A2=D2 next to a row you expect to match. If it says FALSE on two values that look identical, a space or a type mismatch is the cause. TRIM both sides, make sure both are the same type, and add FALSE for an exact match.
Questions
Why does it work for some rows but not others?
The rows that fail have a hidden difference, usually a space or a number stored as text. The ones that work are clean. It is not random, even though it looks that way.
Will XLOOKUP fix this on its own?
Not the space or type problems, since those are in the data. But XLOOKUP does let you set a clearer "not found" message, and it can look in any column, which removes two of the four causes above.
Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.
Excel version
XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.
