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!