There's a better way to build this formula, and it starts with understanding why your current attempts keep failing. You're not alone in this frustration, but the solution is simpler than it seems. The issue isn't your logic, it's the structure of your data. You have a dropdown in column K that pulls from sheet 2, column 1, and you want column I to automatically populate with the correct GL code. That's a straightforward lookup, but the errors you're seeing likely come from how you're setting up your lookup table or how you're referencing your ranges.
Here's the practical fix: instead of trying to nest multiple IF statements or wrestling with XLOOKUP's syntax, build a small reference table on sheet 2 with two columns. One column lists the GL names exactly as they appear in your dropdown, and the other column lists the corresponding codes. So office supplies gets 5200, miscellaneous gets 5300, and so on. Then, in column I, use a simple XLOOKUP that references the dropdown cell in K, points to your reference table, and returns the code. The key is to lock your ranges with dollar signs so the formula copies cleanly down the column. If your dropdown names don't exactly match the reference table entries, that's where the errors creep in. Double-check for extra spaces, capitalization, or typos, because those small mismatches will break the lookup.
What this means for you is that you don't need a complex formula to solve this. You need a clean data structure. Once you have that, the formula becomes almost trivial. And that's the real lesson here: in spreadsheets, the hardest part isn't the formula itself, it's setting up your data so the formula can do its job. You've already done the hard part by clearly defining what you want. Now it's just about giving the tool what it needs to work with. So take a few minutes to verify your reference table, match your names exactly, and then test it with a single row before dragging the formula down.
The fact that you're hitting errors isn't a sign that you're doing something wrong. It's a sign that you're ready to move past the trial-and-error stage and build something more reliable. Start with the reference table, keep your names consistent, and let XLOOKUP handle the rest. You'll have it working in under ten minutes, and you'll wonder why you ever tried to force it with IF statements in the first place.