XLOOKUP vs VLOOKUP: Why You Should Switch in Excel Today

-
XLOOKUP formula in Excel

If you work with lookup tables in Excel, XLOOKUP is one of the most useful formulas you can learn. It was introduced by Microsoft in 2019 as the modern replacement for VLOOKUP and HLOOKUP—so if you haven't come across it yet, that's completely understandable, especially if you learned Excel before then.

If you haven't read my earlier post on HLOOKUP and VLOOKUP, I'd recommend starting there and coming back to this one afterward—this post builds on that foundation.

HLOOKUP vs. VLOOKUP vs. XLOOKUP

  • HLOOKUP—use this when your reference table is laid out horizontally
  • VLOOKUP—use this when your reference table is laid out vertically
  • XLOOKUP—works in either direction and fixes several long-standing annoyances with the older two formulas

Why is XLOOKUP better, not just newer?

  • It can look to the left of the lookup column, which VLOOKUP can't do.
  • It doesn't break if you insert or delete a column in the middle of your data
  • You can specify a custom "not found" value instead of a bare #N/A error.
You want to find John's age and height, and you know his name is in cell A7. The formula would be:

=XLOOKUP(A7, A2:A5, B2:C5)

  • A7—the cell containing the name you're searching for.
  • A2:A5—the range where Excel should look for that name. ("John")
  • B2:C5—the range containing the values you want returned (age and height)
Because the return range covers two columns, Excel will spill the result in two adjacent cells automatically—you'll see 17 in the formula cell and 199 in the cell right next to it. You don't need to press any special key combination; this happens automatically in modern Excel.

If you only need one value, narrow the return range to a single column:

=XLOOKUP(A7, A2:A5, C2:C5)

This returns just 199.

Why this matters for your daily work

This is a simple formula, but it's genuinely useful if you work with data regularly—as an accountant, paralegal, secretary, analyst, or in any role where you're pulling information out of large tables. Imagine a spreadsheet of 1,000 client records: scanning it manually for one name would take forever. XLOOKUP does it instantly.

Even with AI tools making spreadsheet work easier, it's still worth understanding how the underlying formulas work. I've seen students copy formulas as templates without understanding their structure—and then get stuck the moment their data doesn't match the example exactly. Take the time to understand why a formula is built the way it is, and you'll be able to adapt it to any situation, not just the one in the tutorial.

I'll keep sharing practical formulas like this one. Feel free to check back weekly for something new.

Have a great day!

*The top image is a screenshot of the XLOOKUP formula table.

Comments

Post a Comment

Popular posts from this blog

Starting This Blog Again.

-
Image
Hello, and welcome.  A lot has changed in technology since I started working in IT, and I've changed along with it. Over the years, I've worked in different IT roles, from technician to business owner, tutor, and eventually college instructor. Through all these years, one thing has remained constant: learning. That's one of the reasons I've decided to start writing again. This blog will mainly focus on practical IT knowledge and real-world experience—computers, networking, troubleshooting, software, hardware, and other technology-related topics. I'll try to keep things clear, practical, and accessible. But it won't be only about technology. I'll also be sharing stories from my military days . In South Korea, military service is compulsory for most men—and I went in later than most. And as if going late wasn't enough, I ended up at a Ranger School known for exactly one thing: turning you into someone who crawls, rolls, sweats, and questions every life cho...
Read more

Where Do Army Helmets Go?— A Strange Equation Called "Military Math"

-
Image
I went into the army later than most. While other guys came in after their freshman or sophomore year of college, I showed up after finishing grad school—married, no less. So soldiers years younger than me outranked me, and they called me "old man." Being the oldest rookie in the unit made me an easy target for teasing. I'd just been assigned to my unit when the order came down: report for Team Spirit training—the joint U.S.-Korean military exercise. I'd been pulled out as an interpreter. Barely settled in, and already being shipped off again—the thought of adjusting to yet another new environment put me on edge. On my first day at the training site, I was wandering around checking out the base when someone called my name. I turned around—a friend from college! "Hey! Long time no see. You're here for Team Spirit too, huh?" Seeing an old friend there, in uniform and in the middle of a war exercise, felt strangely like meeting up on a real battlefield. He...
Read more

Slide for Life

-
Image
Three Shouts Between Life and Death This happened when I was serving in the military as a Ranger instructor for U.S. soldiers. The American soldiers camped out in a valley at the Ranger school. There were more than thirty courses, ranging from PT and basic obstacles to mountain obstacles, and none of them could be called easy. On top of that, the training was dangerous. What worried me most wasn't the training itself, but safety. On some of the courses, one small mistake could lead to a serious accident, so I repeated the safety instructions again and again. The most dangerous course was called "Slide for Life."  In this exercise, soldiers rode a metal cable about 150 meters down a hillside, dropped into a pond, and then swam to shore. It was a tough course. The hill itself was about the height of a ten-story building, so simply standing at the top could be psychologically overwhelming.  Soldiers with a fear of heights were allowed to do PT instead. But in fact, the most ...
Read more