VLOOKUP vs. HLOOKUP: One Looks Down, the Other Looks Across

-

HLOOKUP and VLOOKUP formula

VLOOKUP and HLOOKUP do the same basic job—but they look in completely different directions.

Here's the easiest way to remember them: VLOOKUP goes vertical. HLOOKUP goes horizontal. That's it.

Picture a potluck at work. You've got a sheet with everyone's name, their department, and what they're bringing. I laid out that same information two ways: a normal table running top to bottom on the left, and the same data flipped sideways on the right, with names running across the top instead.

That's really the key difference between the two. VLOOKUP searches down a column; HLOOKUP searches across a row. Neither one is better. The direction of your table determines which one makes sense.

VLOOKUP: The Vertical Table

If I want to know what Kevin's bringing from the left-hand table, the formula is:

=VLOOKUP(B9, A3:C6, 3, FALSE)

  • B9 is the cell where I typed his name.
  • A3:C6 is the table Excel searches—it scans down the first column looking for a match.
  • 3 tells Excel to return the value from the third column of the table, which happens to be the dish. Change that to a 2, and you'd get his department instead.
  • FALSE tells Excel to look for an exact match. For names, IDs, and other text values, this is usually what you want.

HLOOKUP: The Horizontal Table

Same question, same answer, but the table on the right is built sideways, so the formula changes:

=HLOOKUP(G9, G2:J4, 3, FALSE)

  • G9 is where I typed the name this time.
  • G2:J4 is the table, but now Excel scans across the top row instead of down a column.
  • 3 tells Excel to return the value from the third row of the table, which again lands on the dish.
  • FALSE still means an exact match.

The part that actually confuses most people

The formulas themselves aren't hard to read once you've seen them. What gets people is picking the wrong one for how their sheet is laid out. Try using VLOOKUP on a horizontally arranged table, and you'll usually get an error because VLOOKUP searches vertically, not horizontally.

In real life, you rarely get to choose how a spreadsheet is organized. You inherit whatever someone else already built. Knowing both formulas means you're not stuck rebuilding their layout just to pull one piece of information.

Once you see the direction, choosing between VLOOKUP and HLOOKUP becomes pretty simple: look down, use VLOOKUP; look across, use HLOOKUP.

And yes, there's a newer function that makes both of them look a little old-fashioned. I will cover XLOOKUP later.

* If you're interested, you can check out my other post on How to Calculate Loan Payments in Excel Without AI.


* The top image is a screenshot of an Excel file.

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