top of page
Search
  • Writer's pictureImran Din

Can't quite put a finger on it? Why not Index ... and Match?

People play favorites.

Believe it or not, even Excel experts have their preference when it comes to using VLookup or Index/Match.


I'm a fan of VLookup, but Index/Match can also be a whole lot of fun.


Let's first introduce Match. You see, Match returns the position of a search term within a given array.


Meanwhile, Index plucks out data from a range, if you tell it exactly which row (and column if you're fancy) to look.


And man, when these two homies come together, shit straight up GETS FOUND!


So today we have a dataset with IDs and Names, and we want to write a formula that will return the ID of a given name.



First, we'll use Match to find the name in the list of names. A Match formula looks like this:


=MATCH(searchTerm, lookupArray, [matchType])

In our case, the searchTerm is the name we're looking for, the lookupArray is our names column and the matchType is always 0 for exact matches:



This gives us 2. Which is the position of where the name is found.



Now, we'll use Index. The formula looks something like this:


=INDEX(dataRange, rowIndex)

For our example, the dataRange we're looking at is the ID column. The rowIndex is whatever the Match formula returns:



Let's see it in action:



And there you have it! With INDEX MATCH, you can find that perfect piece of data in the middle of a crowded spreadsheet!


It's like finding your dream date in a crowd or finding the last slice of pizza at a party. Go nuts and happy matching, data chads!

4 views0 comments

댓글


bottom of page