How to Use LOOKUP in Excel

If there’s one Excel function that made me both laugh and cry at the same time, it’s LOOKUP. You know that one friend who’s always helpful but also a bit tricky to understand? Yeah, that’s exactly what LOOKUP feels like. On the surface, it looks simple, but once you dive in, you’ll realize it has its own mysterious rules. And guess what? I learned it the hard way.

So today, I want to share my personal journey (read: struggle) with LOOKUP in Excel. Hopefully, by the end of this story, you’ll laugh at my mistakes, learn from them, and finally master this sneaky little function.

My First Encounter with LOOKUP

Let me take you back to the time when I first discovered LOOKUP. I was working on a report for my boss, and my job was simple: find certain product prices from a long list of items. At first, I thought, “Pfft, easy! I’ll just use VLOOKUP.”

But here’s the twist: my boss gave me a spreadsheet where the data wasn’t neatly organized. The prices were all over the place, the columns didn’t follow the rules of VLOOKUP, and my head started spinning. Then someone whispered the magical word: LOOKUP.

I Googled it quickly, found the formula, and thought: “This is it, my savior!” Spoiler alert: it wasn’t that easy.

What Exactly Is LOOKUP?

For those who are new to this function, let me break it down in a chill way.

LOOKUP is basically like that one person in the office who always knows where everything is, but you have to ask them in the right way. It’s used to search for a value in one row or column, and then return a corresponding value from another row or column.

The syntax looks like this:

=LOOKUP(lookup_value, lookup_vector, result_vector)

Sounds simple, right? But trust me, LOOKUP has its quirks. Unlike VLOOKUP or HLOOKUP, it’s not super strict about columns and rows. Instead, it just does its own thing sometimes, and you’ll be left scratching your head wondering why it returned the wrong value.

My Funny Mistake with LOOKUP

So back to my story. I typed in the formula confidently:

=LOOKUP(“Coffee”, A2:A20, B2:B20)

Here, I wanted to find the price of “Coffee” from column A and return the price from column B. Easy, right?

Well… not really. The first time I ran it, it gave me the price for Tea. I double-checked, rubbed my eyes, even whispered some prayers to the Excel gods, but nope it kept giving me Tea’s price.

After hours of frustration, I finally realized my mistake: LOOKUP only works properly when the data is sorted in ascending order. My list was a mess Coffee was chilling in the middle of the list, far from where LOOKUP expected it to be.

Lesson learned: Always sort your data first before using LOOKUP. Otherwise, you’ll end up like me arguing with your laptop as if it’s alive.

Why LOOKUP Is Still Useful

Now you might be wondering, “If LOOKUP is so confusing, why not just use VLOOKUP or INDEX + MATCH?” Fair question. But LOOKUP has its charm.

Here are some reasons why I still use it sometimes:

1. It’s simple – For small data sets, LOOKUP is way faster to write than VLOOKUP.

2. It works horizontally and vertically – Unlike VLOOKUP that’s stuck with vertical data, LOOKUP is flexible.

3. It’s old-school but reliable – LOOKUP has been around forever, so if you’re working with older versions of Excel, it’s a lifesaver.

Think of it like using a flip phone in the age of iPhones. It’s not the fanciest, but it still does the job.

Step-by-Step: How I Finally Mastered LOOKUP

After that embarrassing Tea vs Coffee fiasco, I decided to sit down and really learn how LOOKUP works. Here’s my step-by-step process:

1. Sort Your Data

LOOKUP is picky. It wants the data sorted in ascending order. If your list isn’t sorted, you’ll get random answers that make no sense.

2. Identify the Lookup Value

This is the thing you’re trying to find like “Coffee,” “Laptop,” or “Banana.”

3. Set Your Lookup Vector

This is the column or row where Excel should look for that value.

4. Choose the Result Vector

This is where Excel will pull the answer from. For example, if you want the price of “Coffee,” your result vector would be the price column.

5. Test with Different Values

Don’t just trust LOOKUP with one item. Try several. This saved me a few headaches later when I realized some values didn’t match what I expected.

My Little Hack with LOOKUP

After using it for a while, I discovered a hack: sometimes it’s easier to just combine LOOKUP with other functions. For example, wrapping LOOKUP with IFERROR makes your life a whole lot easier.

Instead of getting those ugly #N/A errors, you can do this:

=IFERROR(LOOKUP(“Coffee”, A2:A20, B2:B20), “Not Found”)

Now, instead of crying at random errors, you’ll just see a friendly “Not Found” message. Trust me, it feels way better.

The Day I Impressed My Boss

Here’s the fun part: after finally figuring out how to tame LOOKUP, I went back to my boss’s messy spreadsheet. I sorted the data, applied the formula correctly, and boom the prices came out perfectly.

When I presented the results, my boss looked impressed and said, “Wow, that was fast!” Meanwhile, in my head, I was thinking, “If only you knew I spent three hours fighting with Excel yesterday…”

Final Thoughts: Why LOOKUP Is Like That Quirky Friend

At the end of the day, LOOKUP is kind of like that quirky friend we all have. Sometimes confusing, sometimes annoying, but deep down, they’re actually super helpful once you understand them.

So if you’re just starting with LOOKUP, don’t be discouraged. Laugh at the mistakes, learn the quirks, and before you know it, you’ll be the one impressing your boss (or your friends) with your Excel wizardry.

Quick Recap

1. LOOKUP helps you find data in a row or column and return matching results.

2. Always sort your data first LOOKUP hates messy lists.

3. Use IFERROR to make your formulas more user-friendly.

4. Don’t give up after the first mistake Excel functions are like puzzles.

So, that was my funny little adventure with LOOKUP. If you’ve ever yelled at Excel, know that you’re not alone. We’ve all been there. But once you master LOOKUP, you’ll feel like you unlocked a cheat code in life.

Now, what about you? Have you ever had a funny (or frustrating) experience with LOOKUP? If yes, trust me, I’d love to hear it.