XLOOKUP AND $
When selecting the data ranges that have the information you want, enter the $ on the range: XLOOKUP(A5,$D$1:$D$80,$G$1:$G$80)
I'm working 30 years with spreadsheets and I've probably use VLOOKUP without the $ 5 times. People with less skills forget to include the $ and the formulas don't work properly and they have to call me for help.
Kenneth Barber commented
In response to The Thinker 1958, thank you for the clarification. I misinterpreted "enter the $ on the range" as being an instruction for the reader, not the actual suggestion.
In response to Roy, I think that you are reading too much into what I said.
As for the suggestion, I'm actually OK with it. In fact, here is a spin-off suggestion that I just posted:
However, I should mention that the "A1:A9" style of reference is best avoided outside of quick-and-dirty calculations. If all of your data is stored in tables (available from the "Format as Table" on the Home tab), then your formulas will look like this:
rather than this:
Not only is the former formula more readable, but the column references don't change as the formula is dragged down (i.e. the problem in the suggestion is avoided), and the column references grow as the lookup table grows.
You have some mean aspects in your text which gives that observation vs. suggestion observation strong legs.
I've used spreadsheets as long as you and I do have reason to not use them probably 20% of the time with the same lookup functions. And most of the times I do "use" them, I set up a Named Range that is either explicit so I literally use them or dynamic so I "sort of virtually" use them. Plenty of lookups are more ad hoc, boss is asking something I'm not writing into the spreadsheet, just getting him a quick answer.
A thought about those "less skills" or "novice" users: how are they to become something else if everyone babies them? "Protects" them from finding a whole new area that they can pursue new knowledge in?
As a general principle though, a sea-change function like XLOOKUP(), I'd like to see Excel minimize the "of course, you'll want this help and you'll always do this anyway" type things. Mr. Jelen's request for another argument in it, one to handle the "didn't find a match" problem that is 99% of lookup function errors, that's a good add, no "we'll make choices for you" stuff there, just handling a frequent problem. But this kind is asking to have Excel make choices I don't want it making. So I think it a feature I'd hate to see baked in. It subtracts rather than adds like Mr. Jelen's suggestion. Maybe a more practical thought on it: Mr. Barber and I don't seem to agree on much at all in this place, and I think he takes what one could term a "programmer snob" point of view fairly often, but a) he and I agree here so that's a thought-concentrator if you think about it, and b) most of the things I object to in his comments are not wrong, just not seeming to take the circumstances a person might be in into account. He always is, however, always advocating an approach that would help point novices (and upward) in a better direction. This suggestion is the opposite, essentially saying get lost, you're being left behind on purpose, too much trouble all-a-youse. That's not good Mr. 1958.
The Thinker 1958 commented
is a suggestion. the question was "HOW CAN WE IMPROVE EXCEL....".
I suggested that the "$" should be added every time you use VLOOKUP OR XLOOKUP.
I probably used the formula without the "$" on the range 0.0001% of the time.
And is something hard to explain to novice users.
Kenneth Barber commented
This sounds more like an observation than a suggestion.