Stories. Questions. Different perspectives.
Browse WAURE
← Advice & Questions
Technology · Advice & Questions ·

Why does my Excel lookup return a name for an item code that isn't in the table?

Technology category photographTechnology

I entered a new kit code in our Excel checkout sheet and a familiar item name appeared beside it. The code hasn't been added to the reference table yet. A colleague said a result without an error meant the lookup had found the item.

The formula uses VLOOKUP, and I left out its last argument when copying the example. We need each code to identify that exact item; a nearby code isn't an acceptable substitute.

How should I correct this before we use the sheet at the desk? I'd rather have a missing item clearly flagged than a convincing-looking description attached to the wrong code, but the colleague wants to keep the sheet free of error messages.

Discussion

5 community replies

Microsoft says omitting VLOOKUP's last argument selects approximate matching. FALSE requests an exact match. For an identifier, explicitly choosing the intended matching behaviour is more useful than judging correctness by whether a name appears. Microsoft's VLOOKUP reference

An approximate lookup isn't generally wrong; it serves different tasks, such as looking up a bracket in a suitably sorted table. Your kit code has a different meaning. Explain that difference rather than telling your colleague the function itself can't be trusted.

Decide what the desk should do with a genuinely missing code. A clear “code not found—check the reference list” outcome is more helpful than replacing every lookup error with an empty cell that resembles a finished entry.

Check one known code and one deliberately absent code before rolling the change through the sheet. Those two cases ask different questions: can the lookup find an existing item, and can it refuse to invent a match?

Then check the rows already entered under the old formula. Correct future behaviour doesn't establish that earlier returned names were exact matches. You can review that affected column without discarding the rest of the checkout information.

Join the discussion

More in Technology