When it comes to Excel, honestly, there are usually two camps of people. The first camp: die-hard VLOOKUP fans, like that first love you can’t move on from. The second camp: those who finally repent after meeting INDEX + MATCH, realizing: “Wow, there’s actually a more flexible power couple out there!”
In this article, I want to share my personal experience of when I first fell in love with the MATCH function. And no, MATCH here is not a dating app (though it does the same thing: finding something). This function helps you find the position of data inside a range.
My First Encounter with MATCH
I still remember vividly, I was working on a sales report. My boss asked:
“Can you find which position Spicy Fried Chicken Level 5 is in on this month’s menu list?”
In my head, I was like, “Boss, this is Excel, not a queue at a convenience store—why are we looking for positions?”
But since I couldn’t actually say that (or else goodbye bonus), I started looking for a way. That’s when I met MATCH.
The basic formula looks like this:
=MATCH(lookup_value, lookup_array, [match_type])
-
lookup_value → the value you’re searching for.
-
lookup_array → the range where you’re searching.
-
match_type → optional, 1, 0, or -1 (I usually stick with 0 for an exact match).
Here’s a real example. Suppose I had this table:
| Product | Price |
|---|---|
| Fried Chicken Level 1 | 15,000 |
| Fried Chicken Level 2 | 17,000 |
| Fried Chicken Level 3 | 19,000 |
| Fried Chicken Level 4 | 21,000 |
| Fried Chicken Level 5 | 23,000 |
If I wanted to know the position of Fried Chicken Level 5, I could write:
=MATCH("Fried Chicken Level 5", A2:A6, 0)
The result? 5. That means Level 5 is chilling in the 5th row of that range.
At that moment, it felt like I’d just discovered a cheat code in a game. “Wow, finding data positions is actually this easy?”
Why MATCH Is Important
At first, I wondered, “Why bother finding a position? Can’t I just use VLOOKUP or INDEX directly?”
Well, I was so wrong. MATCH is actually the soulmate of INDEX. MATCH finds the position, INDEX grabs the data. They’re like a perfect couple: one’s a detective, the other’s a delivery guy.
Think of it like this:
-
MATCH is Google Maps → tells you where the location is.
-
INDEX is the Uber driver → picks you up from that exact spot.
Without MATCH, INDEX would often get lost. Without INDEX, MATCH would just say, “It’s over there,” without bringing you the actual stuff.
A Funny Experience: Hunting Data in a Giant File
One day, I got handed a gigantic Excel file with thousands of rows. My boss said:
“Find out which row Super Spicy Chili Sauce is in.”
If I scrolled manually, I’d probably find it by sunrise.
Instead, I just typed:
=MATCH("Super Spicy Chili Sauce", A2:A2000, 0)
Boom. Instantly it returned something like 1789.
My boss said: “Wow, that was fast! What trick are you using?”
In my head: “Top secret, Boss. It’s called MATCH.”
If I had explained, maybe he would’ve become a MATCH fanboy too.
MATCH + INDEX = The Dream Team
The more I used MATCH, the more I realized it shines brightest when paired with INDEX.
Say I want to find the price of Fried Chicken Level 4. With MATCH:
=MATCH("Fried Chicken Level 4", A2:A6, 0)
That gives me 4. Nice, but I want the price.
That’s when INDEX steps in:
=INDEX(B2:B6, MATCH("Fried Chicken Level 4", A2:A6, 0))
Bam! The result: 21,000.
That was the moment I realized INDEX + MATCH is Excel’s ultimate power couple. If they were humans, they’d be married and probably trending as #CoupleGoals.
MATCH in Real-Life Scenarios
Over time, I discovered plenty of practical uses for MATCH:
-
Building Dynamic Dashboards
With dropdown menus, I can select a product, MATCH finds its position, and INDEX pulls the related data. The dashboard looks fancy, even though it’s “just formulas.” -
Finding Student Rankings
When a friend asked me to create a student ranking sheet, MATCH was perfect for figuring out the position of each student’s score. Much faster than doing it manually. -
Online Shop Sales Analysis
I helped another friend with his online store. He wanted to know which position his best-selling product held in the product list. MATCH made that super easy.
Tips for Staying Sane with MATCH
From my experience, here are a few survival tips:
-
Always Use 0 for match_type
If you don’t specify, Excel defaults to approximate match, which can give you weird results. Use 0 for exact matches. -
Pair It with INDEX
MATCH alone can tell you the position, but if you want the actual value, let INDEX do the fetching. -
Practice with Everyday Data
Try it with your shopping list, favorite foods, or even (uh oh) a list of exes. Then use MATCH to find their “position.” You’ll get the hang of it quickly.
My Funny Reflection on MATCH
Now that I think about it, MATCH is like that friend who’s a great detective. He doesn’t carry the item himself, but he always knows exactly which shelf it’s on. All you need to do is send INDEX to fetch it.
Back then, I underestimated MATCH because it looked “too simple.” But after using it more, I realized without MATCH, my work would be slower, messier, and headache-inducing.
Now, every time I open Excel, MATCH feels like my secret weapon, ready to strike.
From my personal experience, the MATCH function is one of the keys to becoming an “Excel ninja.” MATCH alone is awesome at finding positions. But combined with INDEX? That’s where the real magic happens.
From sales reports, dynamic dashboards, to student rankings everything gets so much easier with MATCH.
So if you use Excel regularly, don’t just stick with VLOOKUP. Give MATCH a try. Who knows, just like me, you might end up saying:
“Why didn’t I meet MATCH sooner?”
0 Comments:
Post a Comment