The real problem here isn't a missing function or a formatting mismatch, it's that traditional spreadsheets were never built for this kind of work. You have two data sources that should talk to each other, but Excel treats them like strangers. A staff number with an "S" prefix on one sheet and a plain number on another is a trivial difference to a human eye, yet it breaks the lookup entirely. That is not your fault. It is the tool's limitation.
The VLOOKUP attempt with TRUE returns the last name because that mode expects a sorted list and a range lookup, which grabs the closest match rather than an exact one. Switching to FALSE gives #N/A because the formats still do not align. You tried removing the "S" and adding it back, but the underlying data likely hides extra spaces, invisible characters, or differing cell types. Excel's rigidity punishes small inconsistencies that a human would never notice. This is the moment where many users give up or resort to manual entry, but you should not have to accept that.
What you need is a system that understands context, not just cell references. An AI-native spreadsheet can parse the staff number "S111111" and automatically recognize that it corresponds to "111111" in the engineer list, even if the formatting differs. It can handle the lookup without requiring you to clean data first, and it can populate the engineer name as soon as a new job row is added, no VLOOKUP gymnastics required. The goal is not to make you a better formula writer. It is to make the spreadsheet adapt to your data, not the other way around.
Your workflow is a textbook case of a simple automation need being blocked by legacy tooling. The solution exists, and it does not require learning XLOOKUP or upgrading your Office version. It requires a tool that treats data as a conversation, not a calculation. Start by exploring a platform where you can link staff numbers to names without worrying about prefixes or cell formats. That is the practical next step, one that saves you time and frustration, and keeps your logbook accurate without bending your process to fit the software.